Date Parts

Many of the date functions use dates built from date parts.

This table displays allowed values of date-part.

Date part values

Date part

Abbreviation

Values

Year

yy

0001 – 9999

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

mcs or us

0 – 999999

Calyearofweek

cyr

Integer. The year in which the week begins. The week containing the first few days of the year can be part of the last week of the previous year, depending upon which day it begins. If the new year starts on a Thursday through Saturday, its first week starts on the last Sunday of the previous year. If the new year starts on a Sunday through Wednesday, none of its days are part of the previous year.

Calweekofyear

cwk

An integer from 1 to 54 representing the week number within the year that contains the specified date.

Caldayofweek

cdw

The day number within the week (Sunday = 1, Saturday = 7).

Note: By default, Sunday is the first day of the week. To make Monday be the first day, use:
set option ‘Date_First_Day_Of_Week’ = ‘1’
For more information on specifying which day is the first day of the week, see Reference: Statements and Options > Database Options > Alphabetical List of Options > DATE_FIRST_DAY_OF_WEEK Option.

Compatibility

For compatibility with Adaptive Server Enterprise, use the Transact-SQL date and time functions.

Related reference
DATEADD Function [Date and Time]
DATECEILING Function [Date and Time]
DATEDIFF Function [Date and Time]
DATEFLOOR Function [Date and Time]
DATEPART Function [Date and Time]
DATENAME Function [Date and Time]
DATEROUND Function [Date and Time]