Date functions

Search:

TODAY()

Returns a DATE value that represents today’s date

CDATE(Year, Month, Day)

Returns a DATE value built from Year, Month and Day

Year An integer value representing the year (for years preceding 1BC, use negative values) Month An integer value representing the month of the year, in the range 1-12 (1=January) Day An integer value representing the day of the month, in the range 1-31

CDATE(DateString [, Language])

Returns a DATE value by parsing the text DateString, using the optional language Language

DateString: A string defining the date (see below): Language: A supported language or locale identifier that defines the language to use for month names

DateString can be specified in different ways as below

Using YYYY, MM and DD where:

YYYY Is a 4-digit value representing the year MM Is a 2-digit value representing the month of the year DD Is a 2-digit value representing the day of the month

Formats for DateString

YYYY MM DD White space separating the year, month and day values YYYY/MM/DD Oblique (forward slash) character separating the year, month and day values YYYY.MM.DD Period (full stop) character separating the year, month and day values YYYY-MM-DD Hyphen (dash) character separating the year, month and day values -YYYY-MM-DD Hyphen (dash) separator with a negative year value +YYYY-MM-DD Hyphen (dash) separator with a positive year value YYYYMMDD No separation between the year, month and day values

Using Yx, MM and DD where:

Yx Is an integer value, followed by AD, BC, BCE or CE, representing the year MM Is a 2-digit value representing the month of the year DD Is a 2-digit value representing the day of the month

Formats for DateString

Yx MM DD White space separating the year, month and day values Yx/MM/DD Oblique (forward slash) character separating the year, month and day values Yx.MM.DD Period (full stop) character separating the year, month and day values Yx-MM-DD Hyphen (dash) character separating the year, month and day values

Using D, Mn, YYYY and Yx where:

D Is a 1-digit or 2-digit value representing the day of month Mn Is the name of the full name or abbreviated name of the month YYYY Is a 4-digit value representing the year Yx Is an integer value, followed by AD, BC, BCE or CE, representing the year

Formats for DateString

D Mn YYYY White space separating the day, month name and year values D Mn Yx White space separating the day, month name and year values D Mn White space separating the day and month name values (year is take from today)

CENTURY(Date)

Returns the integer value representing the century of Date (dates before 1BC return a negative value)

Date A date value

YEAR(Date)

Returns the integer value representing the year of Date (dates before 1BC return a negative value)

Date A date value

QUARTER(Date)

Returns the integer value representing the year quarter of Date (1-4, 1=Jan-Mar, etc.)

Date A date value

MONTH(Date)

Returns the integer value representing the month of the year of Date (1-12, 1=Jan, etc.)

Date A date value

WEEK(Date)

Returns the integer value representing the week of the year of Date (1-53, following ISO 8601 week definition)

Date A date value

YDAY(Date)

Returns the integer value representing the day of the year of Date (1-366, 1=Jan 1st, etc.)

Date A date value

MDAY(Date) / DAY(Date)

Returns the integer value representing the day of the month of Date (1-31, 1=1st, etc.)

Date A date value

WDAY(Date)

Returns the integer value representing the day of the week of Date (1-7, 1=Monday, etc.)

Date A date value

WEEKSINYEAR(Date)

Returns the integer value that is the number of weeks the year of Date spans (52 or 53)

Date A date value

DAYSINYEAR(Date)

Returns the integer value that is the number of days the year of Date spans (365 or 366)

Date A date value

WEEKSINMONTH(Date)

Returns the integer value that is the number of weeks the month of Date spans (4, 5 or 6)

Date A date value

DAYSINMONTH(Date)

Returns the integer value that is the number of days the month of Date spans (28, 29, 30 or 31)

Date A date value

MONTHNAME(Date, Abbreviated [, Language])

Returns the month name as a text string

Date A date value Abbreviated Return the abbreviated name Language A supported language or locale identifier that defines the language to use

DAYNAME(Date, Abbreviated [, Language])

Returns the weekday name as a text string

Date A date value Abbreviated Return the abbreviated name Language A supported language or locale identifier that defines the language to use

DATEPART(Date, Part)

Returns the integer value representing the requested part of Date

Date A date value Part The part of the date to return as follows:

Part can be:

"c" the century - equivalent to CENTURY(Date) "y" the year - equivalent to YEAR(Date) "q" the year quarter - equivalent to QUARTER(Date) "m" the month of year - equivalent to MONTH(Date) "w" the week of year - equivalent to WEEK(Date) "j" the day of year - equivalent to YDAY(Date) "d" the day of month - equivalent to MDAY(Date) "n" the day of week - equivalent to WDAY(Date) "dy" the number of days the year spans - equivalent to DAYSINYEAR(Date) "dm" the number of days the month spans - equivalent to DAYSINMONTH(Date) "wy" the number of weeks the year spans - equivalent to WEEKSINYEAR(Date) "wm" the number of weeks the month spans - equivalent to WEEKSINMONTH(Date)

DATESTRING(Date [, Template [, Language] ])

Returns a text string based on the Template specifed

Date A date value Template The template for the return string (see below). Language A supported language or locale identifier that defines the language to use for month names

The following sub-strings in Template are replaced:

"{c}" the century (variable number of characters) "{c2}" the century (2 digits with additional leading hyphen for dates before 1BC) "{y}" the year (variable number of characters) "{y2}" the year, excluding the century (2 digits) "{y4}" the year (4 digits with additional leading hyphen for dates before 1BC) "{q}" the year quarter (1 digit) "{m}" the month of year (variable number of characters) "{m2}" the month of year (2 digits) "{w]" the week of year (variable number of characters) "{w2]" the week of year (2 digits) "{j}" the day of year (variable number of characters) "{j3}" the day of year (3 digits) "{d}" the day of month (variable number of characters) "{d2}" the day of month (2 digits) "{n}" the day of week (1 digits) "{M}" the month name (abbreviated) "{MM}" the month name (full) "{D}" the day name (abbreviated) "{DD}" the day name (full)

ISLEAPYEAR(Date)

Returns the 1 if the year Date A leap-year, or 0 if it is not.

Date A date value

DATEDIFF(Date1, Date2, Type)

Returns the integer value representing the difference between Date1 and Date2 (Date1 minus Date2)

Date1 A date value Date2 A date value Type The type of value to return, as below:

"y" Whole years (12 month units) "q" Whole year quarters (3 month units) "m" Whole months (28, 29, 30 or 31 day units) "w" Whole weeks (7 day units) "d" Whole days "Y" Calendar years (years starting 1st Jan) "Q" Calendar year quarters (same as “q”) "M" Calendar months (months starting 1st) "W" Calendar weeks (weeks starting Monday) "D" Calendar days (same as “d”)

DATEADD(Date, Adjustment, Type)

Returns the new date value after adding the adjustment value (equivalent to DATESUB with a negative adjustment value)

Date A date value Adjustment The integer adjustment value Type The type of the adjustment, as below:

"y" years "q" year quarters "m" months "w" weeks "d" days

DATESUB(Date, Adjustment, Type)

Returns the new date value after subtracting the adjustment value (equivalent to DATEADD with a negative adjustment value)

Date A date value Adjustment The integer adjustment value Type The type of the adjustment, as follows:

"y" years "q" year quarters "m" months "w" weeks "d" days

Back to start