There is a couple of straightforward ways to get day of year for a given date in SQL Server. You can use either the DATENAME built-in function or the DATEPART built-in function. Let’s see how to use them:
1. DATENAME Method
You can use the DATENAME built-in function along with the first parameter as dayofyear (or dy or y) to get the day of year. Here you will get the return value of DayOfYear as character string.
Syntax
DATENAME(dayofyear, <GivenDate>)
/* OR */
DATENAME(dy , <GivenDate>)
/* OR */
DATENAME(y , <GivenDate>)
Example
SELECT DATENAME(dayofyear , GetDate()) AS 'Day Of year'
GO
/* Result */
171
SELECT DATENAME(dy , '2019-06-20 12:30:00') AS 'Day Of year'
GO
/* Result */
171
SELECT DATENAME(y , '2019-06-20') AS 'Day Of year'
GO
/* Result */
171
2. DATEPART Method
You can also use the DATEPART built-in function along with the first parameter as dayofyear (or dy or y) to get the day of year. Here you will get the return value of DayOfYear as integer instead of string as we have seen in DATENAME.
Syntax
DATEPART(dayofyear , <GivenDate>)
/* OR */
DATEPART(dy , <GivenDate>)
/* OR */
DATEPART(y , <GivenDate>)
Example
SELECT DATEPART (dayofyear , GetDate()) AS 'Day Of year'
GO
/* Result */
171
SELECT DATEPART (dy , '2019-06-20 12:30:00') AS 'Day Of year'
GO
/* Result */
171
SELECT DATEPART (y , '2019-06-20') AS 'Day Of year'
GO
/* Result */
171
Conclusion
If you want the return value in integer, then use DATEPART method. If you want the return value in character string, then go for DATENAME method.
Reference
- About DATENAME built-in date function at Microsoft Docs.
- About DATEPART byult-in date function at Microsoft Docs.