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.


Search Keys

  • sql dayofyear function
  • sql day of year
  • day of year sql server
  • sql year
  • sql year function
  • sql get year
  • sql date year
  • sql server get year from date
  • sql week of year
  • sql year month
  • get year sql
  • year function sql
  • year month sql
  • sql date get year
  • week of year sql
  • datepart

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
5 SQL Server Fundamentals 19-06-1996 15:43:50
6 Programming With MySQL 14-06-1997 01:46:41
7 Designing Sql Server 2000 Databases 21-01-2010 07:13:24
8 Postgre Sql Server Programming 15-01-1998 13:58:01
9 Programming Microsoft sql Server 2012 18-05-1997 11:41:10
10 SQL Functions Reference 24-01-2001 06:50:50
11 SQL Server: The Complete Reference 23-08-2001 01:37:57
12 SQL and Relational Database Design 19-06-2000 10:43:57

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
19-06-1996 15:43:50 171
14-06-1997 01:46:41 165
21-01-2010 07:13:24 21
15-01-1998 13:58:01 15
18-05-1997 11:41:10 138
24-01-2001 06:50:50 24
23-08-2001 01:37:57 235
19-06-2000 10:43:57 171

Search Keys

  • sql server date functions
  • sql datepart
  • sql date functions
  • sql current date
  • sql date format
  • sql date now
  • sql datetime format
  • sql convert date
  • sql server datetime functions
  • ms sql server date functions
  • sql date compare
  • sql date comparison
  • sql date functions with examples
  • sql get date
  • sql current_date
  • get current date sql