Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
This function returns an integer representing the specified datepart of the specified date.
See Date and Time Data Types and Functions (Transact-SQL) for an overview of all Transact-SQL date and time data types and functions.
DATEPART ( datepart , date )
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
The specific part of the date argument for which
DATEPART will return an integer. This table lists all valid datepart arguments.
DATEPART does not accept user-defined variable equivalents for the datepart arguments.
An expression that resolves to one of the following data types:
DATEPART will accept a column expression, expression, string literal, or user-defined variable. Use four-digit years to avoid ambiguity issues. See Configure the two digit year cutoff Server Configuration Option for information about two-digit years.
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. The return value depends on SET DATEFORMAT if date is a string literal of some formats. SET DATEFORMAT does not change the return value when the date is a column expression of a date or time data type.
This 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 date argument has a datetimeoffset(7) data type. The last two positions of the nanosecond datepart return value are always
00 and this value has a scale of 9:
|year, yyyy, yy||2007|
|quarter, qq, q||4|
|month, mm, m||10|
|dayofyear, dy, y||303|
|day, dd, d||30|
|week, wk, ww||44|
|second, ss, s||32|
|iso_week, isowk, isoww||44|
Week and weekday datepart arguments
For a week (wk, ww) or weekday (dw) datepart, the
DATEPART return value depends on the value set by SET DATEFIRST.
January 1 of any year defines the starting number for the week datepart. For example:
DATEPART (wk, 'Jan 1, xxxx') = 1
where xxxx is any year.
This table shows the return value for the week and weekday datepart for '2007-04-21 ' for each SET DATEFIRST argument. January 1, 2007 falls on a Monday. April 21, 2007 falls on a Saturday. For U.S. English,
SET DATEFIRST 7 -- ( Sunday )
serves as the default. After setting DATEFIRST, use this suggested SQL statement for the datepart table values:
SELECT DATEPART(week, '2007-04-21 '), DATEPART(weekday, '2007-04-21 ')
year, month, and day datepart Arguments
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) covered Monday, 29 December 2003 to Sunday, 4 January 2004. European countries / regions typically use this style of numbering. Non-European countries / regions typically do not use it.
Note: the highest week number in a year could be either 52 or 53.
The numbering systems of different countries/regions might not comply with the ISO standard. This table shows six possibilities:
|First day of week||First week of year contains||Weeks assigned two times||Used by/in|
1-7 days of year
1-7 days of year
|Yes||Most of Europe and the United Kingdom|
4-7 days of year
|No||ISO 8601, Norway, and Sweden|
7 days of year
1-7 days of year
1-7 days of year
DATEPART returns the tzoffset (tz) value as the number of minutes (signed). This statement returns a time zone offset of 310 minutes:
SELECT DATEPART (tzoffset, '2007-05-10 00:00:01.1234567 +05:10');
DATEPART renders the tzoffset value as follows:
- 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 implicitly convert to datetimeoffset or datetime2,
DATEPARTreturns the time offset in minutes. Exception: other date / time data types.
- Parameters of all other types result in an error.
smalldatetime date Argument
For a smalldatetime date value,
DATEPART returns seconds as 00.
Default Returned for a datepart That Is Not in a date Argument
If the date argument data type does not have the specified datepart,
DATEPART will return the default for that datepart only when a literal is specified for date.
For example, the default year-month-day for any date data type is 1900-01-01. This statement has date part arguments for datepart, a time argument for date, and it 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,
DATEPART will return error 9810. In this example, variable @t has a time data type. The example fails because the date part year is invalid for the time data type:
DECLARE @t time = '12:10:30.123'; SELECT DATEPART(year, @t);
These statements show that
DATEPART returns fractional seconds:
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.
DATEPART implicitly casts string literals as a datetime2 type in SQL Server 2019 (15.x). This means that DATENAME 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.
This example returns the base year. The base year helps with date calculations. In the example, a number specifies the date. Notice that SQL Server interprets 0 as January 1, 1900.
SELECT DATEPART(year, 0), DATEPART(month, 0), DATEPART(day, 0); -- Returns: 1900 1 1
This example returns the day part of the date
-- Uses AdventureWorks SELECT TOP(1) DATEPART (day,'12/20/1974') FROM dbo.DimCustomer; -- Returns: 20
This example returns the year part of the date
-- Uses AdventureWorks SELECT TOP(1) DATEPART (year,'12/20/1974') FROM dbo.DimCustomer; -- Returns: 1974
Submit and view feedback for