SQL DAYOFYEAR() Function


The SQL DAYOFYEAR() is a function, and returns a integer indicating day of the year of a given input date value.

The SQL DAYOFYEAR() function is supports only date or datetime based table columns or fields.

It can be used in SELECT statement as well in where clause.

You can also search these topics using sql dayofyear function, day of year sql server, sql year function, sql server get year from date, year function sql, sql date get year.

SQL DAYOFYEAR() Syntax

The below syntax is used to extract day of the year from given a input date value.

For SQL SERVER / MY SQL


SELECT DAYOFYEAR('valid_date_value');

For MS Access


SELECT DATEPART('y', 'valid_date_value');


SQL DAYOFYEAR() Example - Using Expression Or Formula

The following SQL SELECT statement will extract day of the year from a given input date value. We use now() function to get the input date value.

For SQL SERVER / MY SQL


SELECT 
NOW() AS 'Current Date and Time', 
DAYOFYEAR(NOW()) AS 'Day Of The Year';

For MS Access


SELECT 
NOW() AS 'Current Date and Time', 
DATEPART('y', NOW()) AS 'Day Of The Year';

The result of above query is:

Current Date and Time Day Of The Year
4-02-2013 15:48:52 35

Sample Database Table - BookOrder

BookID BookName OrderDate
1 Learing Oracle SQL & Pl/sql 23-02-2000 21:05:54
2 SQL and PL/SQL for Oracle 10g Black Book 20-05-2001 22:22:07
3 Pro SQL Azure 17-07-2004 05:55:04
4 Pro Sql Server 2008 Administration 14-05-2005 02:57:54

SQL DAYOFYEAR() Example - With Table Column

The following SQL SELECT statement display the column "OrderDate", from the "BookOrder" table. The DAYOFYEAR() function will extract day of the year from the column "OrderDate" and we will stored that value to a new column called "Day Of The Year".

For SQL SERVER / MY SQL


SELECT OrderDate, 
DAYOFYEAR(OrderDate) As 'Day Of The Year'
FROM BookOrder;

For MS Access


SELECT OrderDate, 
DATEPART('y', OrderDate) AS 'Day Of The Year'
FROM BookOrder;

The result of above query is:

OrderDate Day Of The Year
23-02-2000 21:05:54 54
20-05-2001 22:22:07 140
17-07-2004 05:55:04 199
14-05-2005 02:57:54 134
You can also search these topics using sql server date functions, sql date functions, sql datetime format, sql server datetime functions, ms sql server date functions, sql date comparison, sql date functions with examples, get current date sql.