CODEWITHSUNDEEP
sql-server-2008
Bhavesh Shah
Bhavesh Shah

Reputation: 3389

DATEDIFF function in SQL Server - executes differently in different cases

I am using SQL Server and just identify one problem in my case. I have used the DATEDIFF function as:

select datediff(dd,'1935-12-07','2010-03-02')/365.00  ---> 74.28
select datediff(dd,'1935-12-07','2010-03-02')/365     ---> 74
select datediff(yy,'1935-12-07','2010-03-02')         ---> 75

If you can observe that If I try DATEDIFF with 'dd' then I get the difference as 74/74.28. But If I use it with 'yy' I get the diference as 75.

Why this is so? Means Why the difference came as 75 as it nearly approximate to 74.

I need this both function in different cases. But as it is behaving differently I am facing a lot of problems.

Suggest me some solution to this.

Thanks.

Upvotes: 0

Views: 1846

Answers (2)

joshuahealy
joshuahealy

Reputation: 3569

The datediff function checks for the difference of the date part you have specified, so it is correct in saying the difference in the year part of the two dates is 75.

Or to word it as the folks at microsoft do, it counts the number of boundaries crossed of that datepart. There are 75 year-boundaries crossed between the two dates in your example.

Have a look at this msdn page, it explains how it is supposed to work.

Upvotes: 1

hkf
hkf

Reputation: 4520

First case = You're implicitly casting to a float, shows the correct result

Second case = You're implictly casting to an int, which rounds down as part of the conversion (Floor value)

Third case = You're not casting anything at all, and DATEDIFF() returns a signed int, which rounds up (Ceiling value)

Solution: Do whatever you want, but keep it consistent throughout your code.

Upvotes: 1

Related Questions