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