CODEWITHSUNDEEP
sqlsql-servert-sqldatediff
Farellia
Farellia

Reputation: 187

How does SQL Server calculate DATEDIFF function?

Okay, so here's a seemingly obvious question, but I'm just not following completely...

I ran the following code on a bunch of dates:

SELECT 
    DATEDIFF(DAY, Start, [End]) AS DIFD,
    DATEDIFF(WEEK, Start, [End]) AS DIFW,
    DATEDIFF(Month, Start, [End]) AS DIFM,
    DATEDIFF(Year, Start, [End]) AS DIFY 
FROM
    Datetest

And here's the result that I'm seeing:

Start       End           Description   DIFD    DIFW    DIFM    DIFY
2010-03-25  2011-03-25  Normal Year     365     52      12      1
2011-03-25  2012-03-25  Leap Year       366     53      12      1
2010-03-24  2011-03-25  Add 1 day       366     52      12      1
2010-03-24  2011-03-26  Add 2 day       367     52      12      1
2010-03-24  2011-03-27  3 days          368     53      12      1
2010-03-24  2011-03-28  4 days          369     53      12      1
2010-03-24  2011-03-29  5 days          370     53      12      1
2010-03-24  2011-03-30  6 days          371     53      12      1
2010-03-24  2011-03-31  7 days          372     53      12      1

So the only thing that makes sense here is the difference by date.

How is it that for weeks, it didn't manage to recognize a leap year of 366 days and classified it as 53 weeks but then went ahead and classified the 3rd record as only 52 weeks when there are the same amount of days?

Similarly, I don't understand the Month and Year. I thought from the 3rd record onwards, it would be rounding up to 13 months or 2 years?

Upvotes: 2

Views: 522

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

DATEDIFF

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

The key word here is "boundaries".

The boundary of a week depends on the server settings (the week can start on Sunday or Monday, or any other day). See SET DATEFIRST

Boundary for months is the 1st day of the month, boundary for years is the 1st of January.

For example, both

DATEDIFF(Year, '2010-03-24', '2011-03-24')
DATEDIFF(Year, '2010-01-01', '2011-12-31')

would return 1, because there is only one 1st of January between both ranges of dates above.

Upvotes: 6

Tom H
Tom H

Reputation: 47454

The DATEDIFF function doesn't calculate an exact number and then round. It counts boundaries crossed. For example, DATEDIFF(YEAR, '2016-01-01', '2016-12-31') would actually be 0.

Upvotes: 4

Related Questions