Overview
The DATEPART function returns an integer value for the specified part of the date or time.
Explanation
Syntax
DATEPART(datepart, datetime)Parameters
- datepart – Is the part of the date we want to get. It can be a year (yy, yyyy), quarter (qq, q), month (mm, m), dayofyear(dy, y), day (dd, d), week (wk, ww), weekday (dw, w), hour (hh), minute (mi, n), second (ss, s), millisecond (ms), microsecond (mcs), nanosecond (ns), TZoffset (tz), ISO_WEEK (ISOWK,ISOWW). You can use the full name or the abbreviations in parenthesis.
- datetime – Is the datetime, date or time used in the function to get part of it.
Simple DATEPART Example
The following example will show the year of the DATEPART specified.
SELECT DATEPART(year, '2020-03-05 2:10:30.123') as datepart
NULL values with DATEPART
If a null parameter is used, the function will return a null value.
SELECT DATEPART(month, NULL) as datepart
Conversion failed when converting date for DATEPART
A typical error is a conversion failure. The following code shows an example:
SELECT DATEPART(day, '2021, march 21')The date is literal and a literal date is not supported with this function and therefore return this error message.
Conversion failed when converting date and/or time from character string.Using DATEPART against a Table
The following example shows the year, month and day for birthdates from the employee table.
SELECT DATEPART(YY, BirthDate) as year,
DATEPART(MM, BirthDate) as month,
DATEPART(dd, BirthDate) as day
FROM HumanResources.Employee
DATEPART Example with Different Units
The following example shows the DATEPART using different units.
SELECT
DATEPART(yy, '2020-03-05 2:10:30.123') as YEAR,
DATEPART(mm, '2020-03-05 2:10:30.123') as MONTH,
DATEPART(DD, '2020-03-05 2:10:30.123') as DAY,
DATEPART(hh, '2020-03-05 2:10:30.123') as HOUR,
DATEPART(mi, '2020-03-05 2:10:30.123') as MINUTE,
DATEPART(ss, '2020-03-05 2:10:30.123') as SECOND,
DATEPART(ms, '2020-03-05 2:10:30.123') as MILLISECOND
Here is another example showing all possible output.
DECLARE @date datetime2 = '2021-01-07 14:36:17.6222691'| DateGroup | DatePart | Query | Result |
|---|---|---|---|
| day | d | SELECT DATEPART(d, @date) | 7 |
| day | day | SELECT DATEPART(day, @date) | 7 |
| day | dd | SELECT DATEPART(dd, @date) | 7 |
| dayofyear | dayofyear | SELECT DATEPART(dayofyear, @date) | 7 |
| dayofyear | dy | SELECT DATEPART(dy, @date) | 7 |
| dayofyear | y | SELECT DATEPART(y, @date) | 7 |
| hour | hh | SELECT DATEPART(hh, @date) | 14 |
| hour | hour | SELECT DATEPART(hour, @date) | 14 |
| microsecond | microsecond | SELECT DATEPART(microsecond, @date) | 622269 |
| microsecond | mcs | SELECT DATEPART(mcs, @date) | 622269 |
| millisecond | millisecond | SELECT DATEPART(millisecond, @date) | 622 |
| millisecond | ms | SELECT DATEPART(ms, @date) | 622 |
| minute | mi | SELECT DATEPART(mi, @date) | 36 |
| minute | minute | SELECT DATEPART(minute, @date) | 36 |
| minute | n | SELECT DATEPART(n, @date) | 36 |
| month | m | SELECT DATEPART(m, @date) | 1 |
| month | mm | SELECT DATEPART(mm, @date) | 1 |
| month | month | SELECT DATEPART(month, @date) | 1 |
| nanosecond | nanosecond | SELECT DATEPART(nanosecond, @date) | 622269100 |
| nanosecond | ns | SELECT DATEPART(ns, @date) | 622269100 |
| quarter | q | SELECT DATEPART(q, @date) | 1 |
| quarter | SELECT DATEPART(qq, @date) | 1 | |
| quarter | quarter | SELECT DATEPART(quarter, @date) | 1 |
| second | s | SELECT DATEPART(s, @date) | 17 |
| second | second | SELECT DATEPART(second, @date) | 17 |
| second | ss | SELECT DATEPART(ss, @date) | 17 |
| week | week | SELECT DATEPART(week, @date) | 2 |
| week | wk | SELECT DATEPART(wk, @date) | 2 |
| week | ww | SELECT DATEPART(ww, @date) | 2 |
| weekday | dw | SELECT DATEPART(dw, @date) | 5 |
| weekday | w | SELECT DATEPART(w, @date) | 5 |
| weekday | weekday | SELECT DATEPART(weekday, @date) | 5 |
| year | year | SELECT DATEPART(year, @date) | 2021 |
| year | yy | SELECT DATEPART(yy, @date) | 2021 |
| year | yyyy | SELECT DATEPART(yyyy, @date) | 2021 |
| TZoffset | TZoffset | SELECT DATEPART(TZoffset, @date) | 0 |
| TZoffset | tz | SELECT DATEPART(tz, @date) | 0 |
| ISO_WEEK | ISO_WEEK | SELECT DATEPART(ISO_WEEK, @date) | 1 |
| ISO_WEEK | ISOWK | SELECT DATEPART(ISOWK, @date) | 1 |
| ISO_WEEK | ISOWW | SELECT DATEPART(ISOWW, @date) | 1 |
DATENAME vs DATEPART Examples
The first example will work with DATENAME:
SELECT 'The year is: ' + DATENAME(yy, '2020-03-05') as example
However, it will fail with DATEPART.
SELECT 'The year is: ' + DATEPART(yy, '2020-03-05') as exampleThe error message is the following.
Conversion failed when converting the varchar value 'The year is: ' to data type int.The following examples will display the same results with DATEPART and DATENAME:
SELECT CONCAT('The year is: ', DATEPART(yy, '2021-03-05')) as year
SELECT CONCAT('The year is: ', DATENAME(yy, '2021-03-05')) as yearReturns the same result for both.
SELECT DATEPART(yy, '2021-03-05') + 4 as YEAR
SELECT DATENAME(yy, '2021-03-05') + 4 as YEARReturns the same result for both.
Additional Information
