datepart

Description

Returns the integer value of the specified part of a datetime value.

Syntax

datepart(date_part, date)

Parameters

date_part

is a date part. Table 2-5 lists the date parts, the abbreviations recognized by datepart, and the acceptable values.

**Table 2-5: Date parts and their values**
Date part	Abbreviation	Values
year	yy	1753 – 9999 (2079 for smalldatetime)
quarter	qq	1 – 4
month	mm	1 – 12
week	wk	1 – 54
day	dd	1 – 31
dayofyear	dy	1 – 366
weekday	dw	1 – 7 (Sun. – Sat.)
hour	hh	0 – 23
minute	mi	0 – 59
second	ss	0 – 59
millisecond	ms	0 – 999
calweekofyear	cwk	1 – 53
calyearofweek	cyr	1753 – 9999
caldayofweek	cdw	1 – 7

When you enter a year as two digits (yy):

Numbers less than 50 are interpreted as 20yy. For example, 01 is 2001, 32 is 2032, and 49 is 2049.
Numbers equal to or greater than 50 are interpreted as 19yy. For example, 50 is 1950, 74 is 1974, and 99 is 1999.

Milliseconds can be preceded by either a colon or a period. If preceded by a colon, the number means thousandths of a second. If preceded by a period, a single digit means tenths of a second, two digits mean hundredths of a second, and three digits mean thousandths of a second. For example, “12:30:20:1” means twenty and one-thousandth of a second past 12:30; “12:30:20.1” means twenty and one-tenth of a second past 12:30.

date: can be either the function getdate, a character string in an acceptable date format, an expression that evaluates to a valid date format, or the name of a datetime or smalldatetime column.

Examples

Example 1

This example assumes a current date of November 25, 1995:

select datepart(month, getdate())

-----------
          11

Example 2

select datepart(year, pubdate) from titles where type = "trad_cook"

 -----------
        1990 
        1985 
        1987

Example 3

select datepart(cwk,’1993/01/01’)

-----------
          53

Example 4

select datepart(cyr,’1993/01/01’)

-----------
        1992

Example 5

select datepart(cdw,’1993/01/01’)

-----------
           5

Usage

datepart, a date function, returns an integer value for the specified part of a datetime value. For more information about date functions, see “Date functions”.
datepart returns a number that follows ISO standard 8601, which defines the first day of the week and the first week of the year. Depending on whether the datepart function includes a value for calweekofyear, calyearofweek, or caldayorweek, the date returned may be different for the same unit of time. For example, if Adaptive Server is configured to use US English as the default language, the following returns 1988:
```
datepart(cyr, "1/1/1989")
```
However, the following returns 1989:
```
datepart(yy, "1/1/1989)
```
This disparity occurs because the ISO standard defines the first week of the year as the first week that includes a Thursday and begins with Monday.

For servers using US English as their default language, the first day of the week as Sunday, and the first week of the year is the week that contains January 4th.
The date part weekday or dw returns the corresponding number when used with datepart. The numbers that correspond to the names of weekdays depend on the datefirst setting. Some language defaults (including us_english) produce Sunday=1, Monday=2, and so on; others produce Monday=1, Tuesday=2, and so on.The default behavior can be changed on a per-session basis with set datefirst.
calweekofyear, which can be abbreviated as cwk, returns the ordinal position of the week within the year. calyearofweek, which can be abbreviated as cyr, returns the year in which the week begins. caldayofweek, which can abbreviated as cdw, returns the ordinal position of the day within the week. You cannot use calweekofyear, calyearofweek, and caldayofweek as date parts for dateadd, datediff and datename.
Since smalldatetime is accurate only to the minute, when a smalldatetime value is used with datepart, seconds and milliseconds are always 0.
The values of the weekday date part are affected by the language setting.

Standards

SQL92 – Compliance level: Transact-SQL extension.

Permissions

Any user can execute datepart.