CODEWITHSUNDEEP
sql-server
Syed Islam
Syed Islam

Reputation: 91

Is there any way to get time difference between two time that cross midnight?

I want to get difference between two time. The difference is ok when the time is within same day. But get wrong result when time cross midnight.

 SELECT DATEDIFF(minute, '08:00',DATEADD(minute,-60, '17:00'))

give me the result 480 which is correct. but 

 SELECT DATEDIFF(minute, '20:00',DATEADD(minute,-60, '05:00'))

Give me the result -960 which is wrong. How can i handle it? I don't want to use DateTime datatype.

Upvotes: 1

Views: 1162

Answers (4)

Harshit Kumar
Harshit Kumar

Reputation: 1

Try the below query...make necessary changes accordingly.

CASE WHEN CONVERT(DATE,DateOutColumn,103) > CONVERT(DATE,DateInColumn,103) THEN ((ABS(DATEDIFF(MINUTE,CONVERT(TIME,TimeInColumn,108),'23:59:00.0000000')))+1+(ABS(DATEDIFF(MINUTE,CONVERT(TIME,TimeOutColumn,108),'00:00:00.0000000'))))/60 ELSE (DATEDIFF(MINUTE,CONVERT(TIME,TimeInColumn,108),CONVERT(TIME,TimeOutColumn,108)))/60

Upvotes: 0

uzi
uzi

Reputation: 4146

Here is one trick. No need to use case. Will work in both cases

SELECT (DATEDIFF(minute, '08:00',DATEADD(minute,-60, '17:00')) + 1440) % 1440
SELECT (DATEDIFF(minute, '20:00',DATEADD(minute,-60, '05:00')) + 1440) % 1440

Output is 480 in both cases

1440 - amount of minutes in one day. Adding 1440 to cases when datediff returns negative value will return correct value. While for second case result will be some value more than 1 day. In that case we need to remove extra value which 1440. For that % 1440 is needed

Upvotes: 1

moein
moein

Reputation: 1

You can use the below code:

SELECT DATEDIFF(minute, '20:00',getdate())

Upvotes: 0

sanatsathyan
sanatsathyan

Reputation: 1763

You can use a case statement and in these circumstances, you need to add a day and get result like,

SELECT DATEDIFF(minute, '20:00',DATEADD(DAY,1,DATEADD(minute,-60, '05:00')))

Upvotes: 1

Related Questions