Returns the integer value of the specified part of a date expression
datepart(date_part {date | time | datetime | bigtime | bigdatetime}))
is a date part. Table 2-5 lists the date parts, the abbreviations recognized by datepart, and the acceptable values.
Date part |
Abbreviation |
Values |
---|---|---|
year |
yy |
1753 – 9999 (2079 for smalldatetime). 0001 to 9999 for date |
quarter |
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 |
microsecond |
us |
0 - 999999 |
calweekofyear |
cwk |
1 – 53 |
calyearofweek |
cyr |
1753 – 9999 (2079 for smalldatetime). 0001 to 9999 for date |
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.
For datetime, smalldatetime, and time types 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.
Microseconds must be preceded by a decimal point and represent fractions of a second.
is an expression of type datetime, smalldatetime, bigdatetime, bigtime, date, time, or a character string in a datetime format.
Finds the microseconds of a bigdatetime:
declare @a bigdatetime select @a = "apr 12, 0001 12:00:00.000001" select datepart(us, @a) ----------- 000001
Assumes a current date of November 25, 1995:
select datepart(month, getdate())
----------- 11
Returns the year of publication from traditional cookbooks:
select datepart(year, pubdate) from titles where type = "trad_cook"
----------- 1990 1985 1987
select datepart(cwk,'1993/01/01')
----------- 53
select datepart(cyr,’1993/01/01’)
----------- 1992
select datepart(cdw,’1993/01/01’)
----------- 5
Find the hours in a time:
declare @a time select @a = "20:43:22" select datepart(hh, @a) ----------- 20
Returns 0 (zero) if an hour, minute, or second portion is requested from a date using datename or datepar) the result is the default time; Returns the default date of Jan 1 1990 if month, day, or year is requested from a time using datename or datepart:
--Find the hours in a date declare @a date select @a = "apr 12, 0001" select datepart(hh, @a) ----------- 0
--Find the month of a time declare @a time select @a = "20:43:22" select datename(mm, @a) ------------------------------ January
When you give a null value to a datetime function as a parameter, NULL is returned.
Returns the specified datepart in the first argument of the specified date, and the second argument, as an integer. Takes a date, time, datetime, bigdatetime, bigtime, or smalldatetime value as its second argument. If the datepart is hour, minute, second, millisecond, or microsecond, the result is 0.
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 U.S. 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 U.S. English as their default language, the first day of the week is 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.You can change the default behavior on a per-session basis with set datefirst. See the datefirst option of the set command for more information.
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 datetime and time are only accurate to 1/300th of a second, when these datatypes are used with datepart, milliseconds are rounded to the nearest 1/300th second.
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.
ANSI SQL – Compliance level: Transact-SQL extension.
Any user can execute datepart.
Datatypes Date and time datatypes
Commands select, where clause