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.
