SQL DATEPART Function Use and Examples

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	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

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.

SQL Server DATEPART Function

MENU

By: Daniel Calbimonte

Syntax

Parameters

Simple DATEPART Example

NULL values with DATEPART

Conversion failed when converting date for DATEPART

Using DATEPART against a Table

DATEPART Example with Different Units

DATENAME vs DATEPART Examples

Related Articles

<< Previous

Next >>

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');