SQL DATEPART Function Use and Examples

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'

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.

Additional Information

• 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

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.

• MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018