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.

Note that DATEPART() function is not part of SQL Standard and only SQL Server supports it.

Introduction to the SQL DATEPART function #

The DATEPART() function returns an integer value that represents a specified part of the date such as year, month and day, of a given date.

Here’s the syntax of the DATEPART() function:

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

In this syntax:

• datepart is the unit in which you want to extract the date component such as year, month, day, hour, minute and second. See the table below for the detail.
• date is a date, time, timestamp, etc. from which you want to extract the component.

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

The following table shows the valid values of the datepart:

SQL DATEPART Function Examples #

Let’s take some example of using the DATEPART function.

Basic DATEPART function example #

The following query uses the DATEPART function to extract various part of a date

SELECT 
    DATEPART(year, '2018-07-21 15:30:20.05') y,
    DATEPART(quarter, '2018-07-21 15:30:20.05') q,
    DATEPART(month, '2018-07-21 15:30:20.05') m,
    DATEPART(dayofyear, '2018-07-21 15:30:20.05') d,
    DATEPART(week, '2018-07-21 15:30:20.05') w,
    DATEPART(hour, '2018-07-21 15:30:20.05') h,
    DATEPART(minute, '2018-07-21 15:30:20.05') m,
    DATEPART(second, '2018-07-21 15:30:20.05') s;Code language: SQL (Structured Query Language) (sql)

Try it

The playground supports a similar function called DATE_PART. Therefore, you’ll see a slightly different statement.

Output:

  y   | q | m | d  | w  | h  | m  |   s
------+---+---+----+----+----+----+-------
 2018 | 3 | 7 | 21 | 29 | 15 | 30 | 20.05Code language: plaintext (plaintext)

Using DATEPART function with table data #

The following statement uses the DATEPART function to find employees who joined the company in 1999:

SELECT
  first_name,
  last_name,
  hire_date
FROM
  employees
WHERE
  DATEPART(YEAR, hire_date) = 1999;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name | last_name  | hire_date
------------+------------+------------
 Diana      | Lorentz    | 1999-02-07
 Luis       | Popp       | 1999-12-07
 Karen      | Colmenares | 1999-08-10
 Kimberely  | Grant      | 1999-05-24Code language: plaintext (plaintext)

Summary #

• Use the SQL DATEPART() function to extract a part of a date from a given date.