By: Daniel Calbimonte
The DATEPART function returns an integer value for the specified part of the date or time.
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 example
The 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 year
Returns the same result for both.
SELECT DATEPART(yy, '2021-03-05') + 4 as YEAR SELECT DATENAME(yy, '2021-03-05') + 4 as YEAR
Returns the same result for both.
Related Articles
- SQL DATENAME
- How to Get Current Date in SQL Server
- Mimic timestamp behavior of other database platforms to store last modified date
- SQL Convert Date to YYYYMMDD
- SQL Server DIFFERENCE Function
- SQL Server CONCAT Function
Last Update: 1/19/2022
