Get Dynamic Clock In or clock time in SQL
Now, I am going to generate dynamic time like real punch time for Clock In or Clock Out. When we implement punch time functionality.
Create a View for Get Random value in SQL. Because SQL not allow RAND() function in user define function. So I am creating a view for this
CREATE VIEW vw_getRANDValue
AS
SELECT RAND() AS Value
Create a user define function to get real punch time. I am passing date of punch and time range in time format.
GO
CREATE FUNCTION [dbo].[FnGetPunchTime]
(@WorkingDate DATETIME,@timeRangeBegin time,@timeRangeEnd time)
RETURNS DATETIME
AS
BEGIN
DECLARE @Result AS DATETIME;
DECLARE @RandomTime DATEtime, @TimeSpanInMSec int = datediff(MILLISECOND,@timeRangeBegin, @timeRangeEnd)
SELECT @RandomTime=DATEADD(MILLISECOND,(SELECT Value FROM vw_getRANDValue) * @TimeSpanInMSec,@timeRangeBegin)
SELECT @Result=CONVERT(DATETIME, CONVERT(varchar, @WorkingDate, 23) + ' ' + CONVERT(varchar, @RandomTime, 114))
RETURN (@Result);
END
I am putting some data and get result using below script
DECLARE @TimeRangeBegin time = '7:45', @TimeRangeEnd time = '09:15',@WorkingDate date='2019-10-11';select [dbo].[FnGetPunchTime](@WorkingDate,@TimeRangeBegin,@TimeRangeEnd) as PunchTime from (values(1),(2),(3),(4),(5),(6),(7)) Y(A)
Out put
Comments
Post a Comment