CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Etienne
Etienne

Reputation: 7201

DATEDIFF Incorrect results

This is my code and the result is 1 but it must be 0 because the duration is not a full minute

DECLARE @Start AS DATETIME = '2013-10-08 09:12:59.000'
DECLARE @End AS DATETIME   = '2013-10-08 09:13:09.000'

SELECT DATEDIFF(MI,@Start, @End)

How can I work around this issue? I am using SQL Server 2008 R2.

Update

It works fine with the seconds.....just not the minutes.

DECLARE @Start AS DATETIME = '2013-10-08 09:12:59.000'
DECLARE @End AS DATETIME   = '2013-10-08 09:13:09.000'

SELECT DATEDIFF(SS,@Start, @End)

Upvotes: 3

Views: 1468

Answers (5)

Konrad Z.
Konrad Z.

Reputation: 1652

To be more precise you can use:

DECLARE @Start AS DATETIME = '2013-10-08 09:12:59.997'
DECLARE @End AS DATETIME   = '2013-10-08 09:13:59.000'

SELECT DATEDIFF(MS,@Start, @End)/60000.00

But you should be aware, that difference between '2013-10-08 09:12:59.001' and '2013-10-08 09:13:59.000' will be in this case also 1.

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44356

As @MartinSmith mentioned, the current solutions are not handling milliseconds, here is a more exact method

declare @t table(date1 datetime, date2 datetime)
insert @t values('2013-10-08 09:12:00', '2013-10-08 09:13:59')
insert @t values('2013-10-08 09:12:30', '2013-10-08 09:13:29')
insert @t values('2013-10-08 09:12:30:111', '2013-10-08 09:13')
insert @t values('2013-10-08 09:12:30:111', '2013-10-08 09:13')
insert @t values('2013-10-08 09:12:30', '2013-10-08 09:12')

select case when date2 > date1 
        then datediff(minute, 0, date2-date1)
        else -datediff(minute, 0, date1 - date2) 
       end
from @t

Upvotes: 2

R S P
R S P

Reputation: 1077

Try this

DECLARE @Start AS DATETIME = '2013-10-08 09:12:59.000'
DECLARE @End AS DATETIME   = '2013-10-08 09:13:09.000'

SELECT DATEDIFF(SS,@Start, @End)/60

Upvotes: 2

Moho
Moho

Reputation: 16563

9:12 to 9:13 = 1 minute

if you want better resolution, use seconds (SS) for your datepart then divide by 60

Upvotes: 2

Szymon
Szymon

Reputation: 43023

DATEDIFF is quite simple and if you compare minutes, it won't take seconds into account.

Change your query to get the difference in seconds and divide it by 60, rounding down.

Upvotes: 3

Related Questions