DAYOFYEAR Function - SQL


Overview


The DAYOFYEAR function in SQL returns the day of the year (1-366) for a given date. It is useful for determining the ordinal day number within the year.

Example:

SELECT DAYOFYEAR('2000-04-15')

Syntax:

SELECT DAYOFYEAR(date)

date is the date from which you want to extract the day of the year.

Sample Data:

first_name birthday
Frank 1994-03-15 00:00:00
Jane 1980-06-14 00:00:00
Ashley 1975-12-05 00:00:00
Glenn 1988-03-23 00:00:00
Kelly 1979-10-11 00:00:00
Richard 1985-08-30 00:00:00
George 1992-02-18 00:00:00
Kyle 1983-04-07 00:00:00
James 1978-09-27 00:00:00
Gustavo 1987-11-21 00:00:00

Example: Hard-coded value


In this example, we are returning the day of year for the date ‘2000-04-15,’ the final output is 106.


Example: Query without the WHERE Statement


In this example, we are returning the day of year for all of the rows in the birthday column.


Example: Query with the WHERE Statement


In this example, we are returning the day of year for the rows in the birthday column where the first_name column equals ‘Kelly.’