SQL Server DATEDIFF: How's dayofyear different from day?

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()?

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.

StackExchange Database Administrators Q#18065, answer score: 5