CODEWITHSUNDEEP
sqlsql-serverdate-arithmetic
Mr. Roshan
Mr. Roshan

Reputation: 1805

SQL DATEDIFF returns wrong result

My SQL query is as below,

SELECT  CAST(-1 * DATEDIFF(hour, '21-APR-2019 11:30:00 AM', '22-APR-2019 
2:15:20 PM') % 24 AS VARCHAR)

Output of above query:- 

-3

Expected Output :-

-2

Upvotes: 0

Views: 197

Answers (1)

forpas
forpas

Reputation: 164089

This is because:

DATEDIFF(hour, '21-APR-2019 11:30:00 AM', '22-APR-2019 
2:15:20 PM')

returns 27 and not 26.
Change to this:

SELECT  CAST(-1 * (DATEDIFF(minute, '21-APR-2019 11:30:00 AM', '22-APR-2019 
2:15:20 PM') / 60) % 24 AS VARCHAR)

Upvotes: 1

Related Questions