SQL Server - Time Difference in 24-Hour Format

Riddhi Valecha

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.

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

geetha velusamy4y ago
qq
Amit Gupta5y ago
@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:
Riddhi Valecha5y ago
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 Singh5y ago
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 Valecha5y ago
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 Singh5y ago
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 Valecha5y ago
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 Singh5y ago
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