SQL Server DATEPART Function


By:

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
DATEPART SIMPLE EXAMPLE

NULL values with DATEPART

If a null parameter is used, the function will return a null value.

SELECT DATEPART(month, NULL) as datepart
DATEPART with NULL values

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 and a table

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
DATEPART units used

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 qq 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
CONTACT datename

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.

concat with DATEPART and DATENAME
SELECT DATEPART(yy, '2021-03-05') + 4 as YEAR
SELECT DATENAME(yy, '2021-03-05') + 4 as YEAR

Returns the same result for both.

Additions with DATEPART and DATENAME

Related Articles


Last Update: 1/19/2022




Comments For This Article




Monday, January 24, 2022 - 5:57:21 PM - Jeff Moden Back To Top (89691)
You said in the article "The date is literal and a literal date is not supported with this function and therefore return this error message." That's not true. The 2nd operand WILL take a date string IF it's a "legal" date string. For example, the following is a legal date string and it works just fine.

SELECT DATEPART(day, '21 march 2021');


download














get free sql tips
agree to terms