SQL DATEPART

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.