CODEWITHSUNDEEP
sqlsql-server
william
william

Reputation: 7664

How is DateDiff implemented in SQL Server?

I use the following code in my statement.

datediff(minute, visit.nexttime, visit.endtime)

the results vary.

I will show a couple of results.

The first one, 24 rounds up to 25 while there is only 26 seconds.

The second one, 32 remains where the second is 14.

The third one, 5 remains 5, fair enough.

The fourth one, 6 rounds up to 7 while there are only 28 seconds.

The fifth one, 3 round up to 4 because of 52 seconds.

The last one, 3 remains 3 even there are 32 seconds.

Why is that?

I use the following code to get my first column.

CONVERT(varchar(6), datediff(second, visit.nexttime, visit.endtime)/3600)
+ ':' + 
RIGHT('0' + CONVERT(varchar(2), (datediff(second, visit.nexttime, visit.endtime) % 3600) / 60), 2)
+ ':' + 
RIGHT('0' + CONVERT(varchar(2), datediff(second, visit.nexttime, visit.endtime) % 60), 2) 
as 'Transaction Time'

Upvotes: 2

Views: 949

Answers (3)

bernd_k
bernd_k

Reputation: 11966

Datediff is a bit strange and rather nonintuitive. And it works this way since old Sybase days.

In your case datediff(m, x, y) completely ignores the seconds.

You have the same problem when you try to calculate ages:

select datediff (y, '2010-12-31', '2011-01-01')

This doesn't mean that the rounded difference is about 1 year, it only says that 1 new year happened.

To get the results you intend, you must take the difference at a more granular base, as you did using seconds.

Upvotes: 1

Jarod Elliott
Jarod Elliott

Reputation: 15670

Reading the documentation here explains why you are getting those results.

The return value is described as this:

Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.

Since you specified minute for the datepart, I would expect this means the seconds are ignored and it just counts how many times the minute changes between the startdate and enddate.

eg.

If the startdate and enddate were 50 seconds apart in the same minute, it would return 0.

If the startdate and enddate were only 5 seconds apart but in different minutes, it would return 1.

Upvotes: 1

Bnjmn
Bnjmn

Reputation: 1999

Please refer to the MySql documentation. It should clarify this for you.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff

Upvotes: -2

Related Questions