Calculate Hour difference between two DateTime in SQL Server

Bhavesh23
 
on Jul 14, 2022 03:50 AM
358 Views

I want to display no of employee is outside more than 1 hrs.

Like "5 employees are outside more than 1 hour."

the date and time is stored in table as per below

V_INDATETIME V_OUTDATETIME

2022-07-05 16:41:35.180 2022-07-05 17:35:32.797

2022-07-05 16:43:51.830 2022-07-05 17:35:57.533

Now i want to count which employee is outside more than 1 hour.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 15, 2022 07:16 AM

Check the query.

According to the Time you can able to check who is outside for more than 1 hour.

CREATE TABLE #Test
(
    V_INDATETIME DATETIME,
    V_OUTDATETIME DATETIME
)

INSERT INTO #Test VALUES('2022-07-05 16:41:35.180','2022-07-05 17:35:32.797')
INSERT INTO #Test VALUES('2022-07-05 16:43:51.830','2022-07-08 17:35:57.533')

SELECT CONVERT(VARCHAR(10),DATEDIFF(HOUR, V_INDATETIME, V_OUTDATETIME)%24) + ':'
    + CONVERT(VARCHAR(10),DATEDIFF(MINUTE,  V_INDATETIME, V_OUTDATETIME)%3600/60) + ':'
    + CONVERT(VARCHAR(10),(DATEDIFF(SECOND, V_INDATETIME, V_OUTDATETIME)%60)) AS [Time] 
FROM #Test

DROP TABLE #Test

Output

Time

1:0:57

1:12:6