Updated: August 29, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns an integer that represents the specified datepart of the specified date.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

Topic link icon Transact-SQL Syntax Conventions

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  
DATEPART ( datepart , date )  

Is the part of date (a date or time value) for which an integer will be returned. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.

yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
minutemi, n
secondss, s
ISO_WEEKisowk, isoww

Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable, or string literal.

To avoid ambiguity, use four-digit years. For information about two digits years, see Configure the two digit year cutoff Server Configuration Option.


Each datepart and its abbreviations return the same value.

The return value depends on the language environment set by using SET LANGUAGE and by the Configure the default language Server Configuration Option of the login. If date is a string literal for some formats, the return value depends on the format specified by using SET DATEFORMAT. SET DATEFORMAT does not affect the return value when the date is a column expression of a date or time data type.

The following table lists all datepart arguments with corresponding return values for the statement SELECT DATEPART(datepart,'2007-10-30 12:15:32.1234567 +05:10'). The data type of the date argument is datetimeoffset(7). The nanoseconddatepart return value has a scale of 9 (.123456700) and the last two positions are always 00.

datepartReturn value
year, yyyy, yy2007
quarter, qq, q4
month, mm, m10
dayofyear, dy, y303
day, dd, d30
week, wk, ww45
weekday, dw1
hour, hh12
minute, n15
second, ss, s32
millisecond, ms123
microsecond, mcs123456
nanosecond, ns123456700
TZoffset, tz310

When datepart is week (wk, ww) or weekday (dw), the return value depends on the value that is set by using SET DATEFIRST.

January 1 of any year defines the starting number for the weekdatepart, for example: DATEPART (wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

The following table lists the return value for week and weekdaydatepart for '2007-04-21 ' for each SET DATEFIRST argument. January 1 is a Monday in the year 2007. April 21 is a Saturday in the year 2007. SET DATEFIRST 7, Sunday, is the default for U.S. English.





The values that are returned for DATEPART (year, date), DATEPART (month, date), and DATEPART (day, date) are the same as those returned by the functions YEAR, MONTH, and DAY, f respectively.

ISO 8601 includes the ISO week-date system, a numbering system for weeks. Each week is associated with the year in which Thursday occurs. For example, week 1 of 2004 (2004W01) ran from Monday 29 December 2003 to Sunday, 4 January 2004. The highest week number in a year might be 52 or 53. This style of numbering is typically used in European countries/regions, but rare elsewhere.

The numbering system in different countries/regions might not comply with the ISO standard. There are at least six possibilities as shown in the following table

First day of weekFirst week of year containsWeeks assigned two timesUsed by/in
Sunday1 January,

First Saturday,

1–7 days of year
YesUnited States
Monday1 January,

First Sunday,

1–7 days of year
YesMost of Europe and the United Kingdom
Monday4 January,

First Thursday,

4–7 days of year
NoISO 8601, Norway, and Sweden
Monday7 January,

First Monday,

7 days of year
Wednesday1 January,

First Tuesday,

1–7 days of year
Saturday1 January,

First Friday,

1–7 days of year

The TZoffset (tz) is returned as the number of minutes (signed). The following statement returns a time zone offset of 310 minutes.

SELECT DATEPART (TZoffset, '2007-05-10  00:00:01.1234567 +05:10');  

  • For datetimeoffset and datetime2, TZoffset returns the time offset in minutes, where the offset for datetime2 is always 0 minutes.
  • For data types that can be implicitly converted to datetimeoffset or datetime2, with the exception of the other date/time data types, it returns the time offset in minutes.
  • Parameters of all other types result in an error.

When date is smalldatetime, seconds are returned as 00.

If the data type of the date argument does not have the specified datepart, the default for that datepart will be returned only when a literal is specified for date.

For example, the default year-month-day for any date data type is 1900-01-01. The following statement has date part arguments for datepart, a time argument for date, and returns 1900, 1, 1, 1, 2.

SELECT DATEPART(year, '12:10:30.123')  
    ,DATEPART(month, '12:10:30.123')  
    ,DATEPART(day, '12:10:30.123')  
    ,DATEPART(dayofyear, '12:10:30.123')  
    ,DATEPART(weekday, '12:10:30.123');  

If date is specified as a variable or table column and the data type for that variable or column does not have the specified datepart, error 9810 is returned. The following code example fails because the date part year is not a valid for the time data type that is declared for the variable @t.

DECLARE @t time = '12:10:30.123';   
SELECT DATEPART(year, @t);  

Fractional seconds are returned as shown in the following statements:

SELECT DATEPART(millisecond, '00:00:01.1234567'); -- Returns 123  
SELECT DATEPART(microsecond, '00:00:01.1234567'); -- Returns 123456  
SELECT DATEPART(nanosecond,  '00:00:01.1234567'); -- Returns 123456700  

DATEPART can be used in the select list, WHERE, HAVING, GROUP BY and ORDER BY clauses.

In SQL Server 2016, DATEPART implicitly casts string literals as a datetime2 type. This means that DATEPART does not support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.

The following example returns the base year. The base year is useful for date calculations. In the example, the date is specified as a number. Notice that SQL Server interprets 0 as January 1, 1900.

SELECT DATEPART(year, 0), DATEPART(month, 0), DATEPART(day, 0);  
-- Returns: 1900    1    1 */  

The following example returns the day part of the date 12/20/1974.

-- Uses AdventureWorks  
SELECT TOP(1) DATEPART (day,'12/20/1974') FROM dbo.DimCustomer;  

Here is the result set.



The following example returns the year part of the date 12/20/1974.

-- Uses AdventureWorks  
SELECT TOP(1) DATEPART (year,'12/20/1974') FROM dbo.DimCustomer;  

Here is the result set.



CAST and CONVERT (Transact-SQL)

Community Additions