Getting Day Of Year In SQL Server

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
Get Day Of Year In SQL Server

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
Get Day Of Year In SQL Server

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

Related Articles & Tools

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.