In SQL Server, the T-SQL DATEPART() function returns an integer that represents the specified datepart of the specified date. For example, you can pass in 2021-01-07 and have SQL Server return only the year portion (2021).

You can also extract the time portion. For example, you can pass in 2018-06-02 08:24:14.3112042 and have SQL Server return only the hour portion (8).

Examples below.

Syntax

First, the syntax. The DATEPART() syntax goes like this:

DATEPART ( datepart , date )

Where datepart is the part of date (a date or time value) for which an integer will be returned.

The datepart argument can be any of the following:

datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns
tzoffset	tz
iso_week	isowk, isoww

Example 1

Here’s a basic example where I return the year component from a date.

SELECT DATEPART(year, '2021-01-07') AS Result;

Result:

+----------+
| Result   |
|----------|
| 2021     |
+----------+

Example 2

In this example I assign a date to a variable, then I use multiple instances of DATEPART() to retrieve different parts of that date.

DECLARE @date datetime2 = '2018-06-02 08:24:14.3112042';
SELECT 
    DATEPART(year, @date) AS Year,
    DATEPART(quarter, @date) AS Quarter,
    DATEPART(month, @date) AS Month,
    DATEPART(dayofyear, @date) AS 'Day of Year',
    DATEPART(day, @date) AS Day,
    DATEPART(week, @date) AS Week,
    DATEPART(weekday, @date) AS Weekday;

Result:

+--------+-----------+---------+---------------+-------+--------+-----------+
| Year   | Quarter   | Month   | Day of Year   | Day   | Week   | Weekday   |
|--------+-----------+---------+---------------+-------+--------+-----------|
| 2018   | 2         | 6       | 153           | 2     | 22     | 7         |
+--------+-----------+---------+---------------+-------+--------+-----------+

Example 3

You can also retrieve the various time parts from the date.

DECLARE @date datetime2 = '2018-06-02 08:24:14.3112042';
SELECT 
    DATEPART(hour, @date) AS Hour,
    DATEPART(minute, @date) AS Minute,
    DATEPART(second, @date) AS Second,
    DATEPART(millisecond, @date) AS Millsecond,
    DATEPART(microsecond, @date) AS Microsecond,
    DATEPART(nanosecond, @date) AS Nanosecond;

Result:

+--------+----------+----------+--------------+---------------+--------------+
| Hour   | Minute   | Second   | Millsecond   | Microsecond   | Nanosecond   |
|--------+----------+----------+--------------+---------------+--------------|
| 8      | 24       | 14       | 311          | 311204        | 311204200    |
+--------+----------+----------+--------------+---------------+--------------+

Example 4

You can also retrieve the TZoffset and the ISO_WEEK datepart.

The TZoffset is returned as the number of minutes (signed). The ISO_WEEK datepart refers to the ISO week-date system (part of the ISO 8601 standard) which is a numbering system for weeks.

DECLARE @date datetimeoffset = '2018-06-02 08:24:14.3112042 +08:00';
SELECT 
    DATEPART(TZoffset, @date) AS 'Time Zone Offset',
    DATEPART(ISO_WEEK, @date) AS 'ISO_WEEK';

Result:

+--------------------+------------+
| Time Zone Offset   | ISO_WEEK   |
|--------------------+------------|
| 480                | 22         |
+--------------------+------------+

In this example, I set the date to a datetimeoffset format, which includes the timezone offset. The TZoffset is returned in minutes.

A Quick Alternative

T-SQL also offers a more concise way to return the day, month, or year components from a date with the DAY(), MONTH() and YEAR() functions.

They work like this:

DECLARE @date datetime2 = '2018-06-02 08:24:14.3112042';
SELECT 
    DAY(@date) AS DAY,
    MONTH(@date) AS MONTH,
    YEAR(@date) AS YEAR;

Result:

+-------+---------+--------+
| DAY   | MONTH   | YEAR   |
|-------+---------+--------|
| 2     | 6       | 2018   |
+-------+---------+--------+

Getting the Month Name or Day Name

If you need to get the month name or day name, use the DATENAME() function instead. This function returns the results as a character string, and you can return the month and weekday by name. Here are some DATENAME() examples to demonstrate what I mean.