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
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
