datepart

Description

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.

Syntax

datepart(date_part {date | time | datetime | bigtime | bigdatetime}))

Parameters

date_part

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

Table 2-9: Date parts and their values

Date part

Abbreviation

Values

year

yy

1753 – 9999 (2079 for smalldatetime). 0001 to 9999 for date

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

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.

date_expression

is an expression of type datetime, smalldatetime, bigdatetime, bigtime, date, time, or a character string in a datetime format.

Examples

Example 1

Finds the microseconds of a bigdatetime:

declare @a bigdatetime
select @a = "apr 12, 0001 12:00:00.000001"
select datepart(us, @a)
-----------
000001

Example 2

Assumes a current date of November 25, 1995:

select datepart(month, getdate())
-----------
          11

Example 3

Returns the year of publication from traditional cookbooks:

select datepart(year, pubdate) from titles 
    where type = "trad_cook"
 -----------
        1990 
        1985 
        1987 

Example 4

select datepart(cwk,'1993/01/01')
-----------
          53

Example 5

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

Example 6

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

Example 7

Find the hours in a time:

declare @a time
select @a = "20:43:22"
select datepart(hh, @a)
-----------
    20

Example 8

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.

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute datepart.

See also

Datatypes Date and time datatypes

Commands select, where clause

Functions dateadd, datediff, datename, getdate