Date functions
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