Date parts

Many of the date functions use dates built from date parts. Table 5-5 displays allowed values of date-part.

Table 5-5: 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

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

NoteBy default, Sunday is the first day of the week. To make Monday be the first day, set the following option:

set option ‘Date_First_Day_Of_Week’ = ‘1’

For more information on specifying which day is the first day of the week, see “DATE_FIRST_DAY_OF_WEEK option”.

Compatibility

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