TSQL provides the DATEPART() function, which enables us to return the day of the year for a given date in SQL Server.

By “day of the year”, I mean the day number of the given year.

Example

To return the day of the year, use dayofyear as the value for the first argument.

DECLARE @date date = '2020-10-25';
SELECT DATEPART(dayofyear, @date);

Result:

299

Alternative Arguments

Alternatively, you can use dy or y as the values for the first argument. They all return the same output.

DECLARE @date date = '2020-12-31';
SELECT 
    DATEPART(dayofyear, @date) AS dayofyear,
    DATEPART(dy, @date) AS dy,
    DATEPART(y, @date) AS y;

Result:

+-------------+------+-----+
 | dayofyear   | dy   | y   |
 |-------------+------+-----|
 | 366         | 366  | 366 |
 +-------------+------+-----+ 

In this case, the date falls on a leap year.