SQL Server - Time Difference in 24-Hour Format

Question

Hello TEam,

I am again stuck up with SQL Query.

I have the following data -

EMPCODE	PUNCHDATE	PUNCHTIME	INOUT	NAME
187729	02-01-2020	23:31:40	O	Atul
187729	03-01-2020	15:31:40	O	Atul
187729	04-01-2020	03:48:40	O	Atul
187729	06-01-2020	05:31:48	O	Atul
187729	07-01-2020	14:46:39	O	Atul
187729	08-01-2020	15:32:34	O	Atul
187729	09-01-2020	14:47:55	O	Atul
187729	10-01-2020	15:31:23	O	Atul
187729	11-01-2020	18:36:33	O	Atul
187729	13-01-2020	15:33:36	O	Atul

Here, the employee "Atul" has shift from 04:35PM to 11:00PM.

Now, here I need the total number of hours extra that "Atul" has worked - i.e. after 11.00PM, i.e. here where punchtime is "23:31".

How do I calculate the difference between "23:00" and "23:31".

For Punchtime "15:33:36", it is - 11PM to 3PM ->4hrs. This is not coming from Datediff function.

Please guide.

Amit Gupta · Answer

@Riddhi There might be INTIME as well which is missing in your question and shift table of each employee as well. Please share complete data, so one can examine and share you the best possible solution. Update: However, You can use datediff function for the total number of hours DECLARE @StartTime datetime = '2020-12-16 23:00:00.000' , @EndTime datetime = '2020-12-17 03:00:00.000' select DATEDIFF( HOUR , @StartTime, @EndTime) as hours output:

geetha velusamy · Answer

qq

Riddhi Valecha · Answer

But, here - 15:00:00 is 3pm for the same day. In my case, Atul have worked from 4.35pm to 11pm - means he has completed his shift, and worked extra till 3am next day. I need difference for extra working hours - from 11pm to 3am -4hrs. select convert(time, CONVERT(varchar(15),CAST('03:00' AS TIME),100) - CONVERT(varchar(15),CAST('23:00' AS TIME),100)) In this query, I am getting AM and PM Correctly, i.e. - CONVERT(varchar(15),CAST('03:00' AS TIME),100) - this is 3AM. How to get difference from 11pm/23:00pm to next day 3.00am = 4hrs ? In this query, I am getting 4hrs- select datediff(hour , CONVERT(varchar(15),CAST('23:00' AS TIME),100), dateadd(dd,1,CONVERT(varchar(15),CAST('03:00' AS TIME),100)) ) But, this query is applicable only for AM time, how do I do it for all the timestamps?

Sachin Singh · Answer

then keep the endTime fixed as 23:00:00 for example DECLARE @StartTime datetime = '15:33:36.000' , @EndTime datetime = '23:00:00.000' SELECT CAST(@EndTime - @StartTime as Time) As TimeDifference SELECT DATEPART(HOUR, CAST(@EndTime - @StartTime as Time)) Op:- 07:26:24.0000000 OP:- 7

Riddhi Valecha · Answer

Here, Shift Timing for employee Atul is - 4.35PM to 11.00PM. In 24-hour format, it is 4.35 to 23. Now, in some days, he was worked for more than 11PM(more than 23PM) So, those days timings are - 4.35PM to 23.59PM (4.35PM to 12AM) - Extra Hours - from 11pm to 12PM - 1hr 4.35PM to 33.00AM (4.35PM to 03AM) - Extra Hours - 11pm to 3pm -4hrs ----- How do I calculate this?

Sachin Singh · Answer

okk , in sql server you can proceed like following DECLARE @StartTime datetime = '2020-05-10 02:25:34.000' , // if it is only time ,thats okk as well @EndTime datetime = '2020-05-10 03:31:00.000' SELECT CAST(@EndTime - @StartTime as Time) As TimeDifference

Riddhi Valecha · Answer

Hello sir, I need to get the hours in sql query... There are more than 500 records, if I use timespan in asp.net, then portal will run slowly. How do I do it in SQL Query?

Sachin Singh · Answer

why don't you simply use timespan difference string startTime = "7:00" ; string endTime = "14:00" ; TimeSpan duration = DateTime.Parse(endTime).Subtract(DateTime.Parse(startTime)); Console.WriteLine(duration); // OP:- 7:00:00 means 7 hrs,0 mints,0 secs var hrs= duration.Hours var minutes=duration.Minutes