Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

When I look at the DATEDIFF() function on SQL Server, I see that it takes a datepart as its first parameter.

Possible datepart values include day and dayofyear.

I understand that dayofyear is the day number of the year (so that Feb 2 is 33, for example) and day is the day of the month (so that Feb 2 is 2). However, I don't understand the difference when it comes to the DATEDIFF function.

Here are some examples:

select DATEDIFF(dayofyear, '2012-01-01', '2012-02-02')
returns: 32

select DATEDIFF(day, '2012-01-01', '2012-02-02')
returns: 32

select DATEDIFF(dayofyear, '2011-02-01', '2012-02-02')
returns: 366

select DATEDIFF(day, '2011-02-01', '2012-02-02')
returns 366

Are dayofyear and day equivalent for the purposes of DATEDIFF()?

share|improve this question
up vote 4 down vote accepted

Yes it is the same effect for DATEDIFF(). For an arithmatetic function like DATEDIFF(), it will have the same result, as the math is still taking into account the same unit: whether it's for day or dayofyear they are both still in days.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.