title: "DATEPART (Transact-SQL)"

description: "Transact-SQL reference for the DATEPART function. This function returns an integer corresponding to the datepart of a specified date."

ms.prod: sql

ms.technology: t-sql

ms.topic: reference

f1_keywords:

- "dates [SQL Server], dateparts"

dev_langs:

- "TSQL"

monikerRange: ">= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || >= sql-server-linux-2017 || = azuresqldb-mi-current"

This function returns an integer representing the specified *datepart* of the specified *date*.

[!INCLUDE[sql-server-tsql-previous-offline-documentation](../../includes/sql-server-tsql-previous-offline-documentation.md)]

*datepart*

The specific part of the *date* argument for which `DATEPART` will return an **integer**. This table lists all valid *datepart* arguments.

For *date*, `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](../../database-engine/configure-windows/configure-the-two-digit-year-cutoff-server-configuration-option.md) for information about two-digit years.

**int**

Each *datepart* and its abbreviations return the same value.

The return value depends on the language environment set by using [SET LANGUAGE](../../t-sql/statements/set-language-transact-sql.md), and by the [Configure the default language Server Configuration Option](../../database-engine/configure-windows/configure-the-default-language-server-configuration-option.md) of the login. The return value depends on [SET DATEFORMAT](../../t-sql/statements/set-dateformat-transact-sql.md) 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:

|*datepart*|Return value|

|---|---|

|**iso_week, isowk, isoww**|44|

For a **week** (**wk**, **ww**) or **weekday** (**dw**) *datepart*, the `DATEPART` return value depends on the value set by [SET DATEFIRST](../../t-sql/statements/set-datefirst-transact-sql.md).

January 1 of any year defines the starting number for the **week** _datepart_. For example:

|6|17|1|

|7|16|7|

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](../../t-sql/functions/year-transact-sql.md), [MONTH](../../t-sql/functions/month-transact-sql.md), and [DAY](../../t-sql/functions/day-transact-sql.md), 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) 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.

|Wednesday|1 January,<br /><br /> First Tuesday,<br /><br /> 1-7 days of year|Yes||

|Saturday|1 January,<br /><br /> First Friday,<br /><br /> 1-7 days of year|Yes||

`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**, `DATEPART` returns the time offset in minutes. Exception: other date / time data types.

- Parameters of all other types result in an error.

For a [smalldatetime](../../t-sql/data-types/smalldatetime-transact-sql.md) *date* value, `DATEPART` returns seconds as 00.

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

```

These statements show that `DATEPART` returns fractional seconds:

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 [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)]. 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 [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] interprets 0 as January 1, 1900.

```

[CAST and CONVERT &#40;Transact-SQL&#41;](../../t-sql/functions/cast-and-convert-transact-sql.md)