SQL DATEPART Function

Summary: in this tutorial, you will learn how to use the SQL DATEPART() function to return a specified part of a date such year, month, and day from a given date.

The DATEPART() function returns an integer value that represents a specified part of the date of a given date.

The following illustrates the syntax of the DATEPART() function:

DATEPART(datepart, date)
Code language: SQL (Structured Query Language) (sql)

Note that DATEPART() function is not an SQL-standard function and only SQL Server supports it.

Arguments #

datepart #

The datepart is the specific part of the date argument. The following table lists all valid datepart values:

date #

The date is a date literal or an expression from which the part of the date should be extracted.

Return #

The DATEPART() function returns an integer value whose unit is denoted by the datepart argument.

Examples #

The following example extracts the year from a date:

SELECT DATEPART(year, '2018-07-21 15:30:20.05') year;
Code language: SQL (Structured Query Language) (sql)

The output is:

year
-----------
2018
Code language: SQL (Structured Query Language) (sql)

The following example illustrates how to extract the quarter from a date:

SELECT DATEPART(quarter, '2018-07-21 15:30:20.05') quarter
Code language: SQL (Structured Query Language) (sql)

The result is:

quarter
-----------
3
Code language: SQL (Structured Query Language) (sql)

To extract the month from a date, you use the following statement:

SELECT DATEPART(month, '2018-07-21 15:30:20.05') month
Code language: SQL (Structured Query Language) (sql)

Here is the result:

month
-----------
7
Code language: SQL (Structured Query Language) (sql)

To return the day of the year from a date, you use the pass the dayofyear to the first argument of the DATEPART() function:

SELECT DATEPART(dayofyear, '2018-07-21 15:30:20.05') dayofyear
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

dayofyear
-----------
202
Code language: SQL (Structured Query Language) (sql)

To get the day of a given date, you use the following example:

SELECT DATEPART(day, '2018-07-21 15:30:20.05') day
Code language: SQL (Structured Query Language) (sql)

The output is shown as follows:

dayofyear
-----------
21
Code language: SQL (Structured Query Language) (sql)

To extract the week from a date, you use the following statement:

SELECT DATEPART(week, '2018-07-21 15:30:20.05') week
Code language: SQL (Structured Query Language) (sql)

Here is the output:

week
-----------
29
Code language: SQL (Structured Query Language) (sql)

The following example returns the weekday of a date:

SELECT DATEPART(weekday, '2018-07-21 15:30:20.05') weekday
Code language: SQL (Structured Query Language) (sql)

The statement returns the following output:

weekday
-----------
7
Code language: SQL (Structured Query Language) (sql)

To get time information from a date such as an hour, minute, and second, you use the following statement:

SELECT DATEPART(hour, '2018-07-21 15:30:20.05') hour,
    DATEPART(minute, '2018-07-21 15:30:20.05') minute,
    DATEPART(second, '2018-07-21 15:30:20.05') second;
Code language: SQL (Structured Query Language) (sql)

The output is:

hour        minute      second
----------- ----------- -----------
15          30          20
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL DATEPART() function to extract a part of a date from a given date.