List of SQL Date and Time Functions
CURRENT_DATE()
Returns the current date.
Example: SELECT CURRENT_DATE() returns the current date in mm/dd/yy format.
CURRENT_TIME()
Returns the current local time.
Example: SELECT CURRENT_TIME() returns the current date and time in mm/dd/yy hh:mm:ss AM/PM format.
CURRENT_TIMESTAMP()
Returns the current local date and local time as a timestamp value. CONNX returns a value that is accurate to the millisecond.
Example: SELECT CURRENT_TIMESTAMP() returns the current local date and time in mm/dd/yy hh:mm:ss AM/PM format.
CURDATE()
Returns the current date.
Example: SELECT CURDATE() returns the current date in mm/dd/yy format.
CURTIME()
Returns the current local time.
Example: SELECT CURTIME() returns the current local date and time in mm/dd/yy hh:mm:ss AM/PM format.
DAYNAME(date_exp)
Returns a character string containing the data source/specific name of the day (for example, Sunday through Saturday).
Example: SELECT DAYNAME({d '2001-09-31'}) returns Thursday.
DAYOFMONTH(date_exp)
Returns the day of the month based on the month field in date_exp as an integer value in the range of 1-31.
Example: SELECT DAYOFMONTH({d '2001-09-13'}) returns 13.
DAYOFWEEK(date_exp)
Returns the day of the week based on the week field in date_exp as an integer value in the range of 1-7, where 1 represents Sunday.
Example: SELECT DAYOFWEEK({d '2001-09-13'}) returns 5 (Thursday as the fifth day of the week).
DAYOFYEAR(date_exp)
Returns the day of the year based on the year field in date_exp as an integer value in the range of 1-366.
Example: SELECT DAYOFYEAR({d '2001-09-13'}) returns 256.
EXTRACT(extract-field FROM extract-source)
Returns the extract-field portion of the extract-source. The extract-source argument is a date or timestamp expression.
The extract-field argument can be one of the following keywords:
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
Example: SELECT EXTRACT(YEAR FROM {d '2001-09-13'}) returns 2001.
HOUR(time_exp)
Returns the hour based on the hour field in time_exp as an integer value in the range of 0-23.
Example: SELECT HOUR({t '10:00:00'}) returns 10 (ten o'clock is the tenth hour of the day).
MINUTE(time_exp)
Returns the minute based on the minute field in time_exp as an integer value in the range of 0-59.
Example: SELECT MINUTE({t '10:33:00'}) returns 33.
MONTH(date_exp)
Returns the month based on the month field in date_exp as an integer value in the range of 1-12.
Example: SELECT MONTH({d '2000-09-13'}) returns 9.
MONTHNAME(date_exp)
Returns a character string containing the data source/specific name of the month (for example, January through December). Currently only supports English locale.
Example: SELECT MONTHNAME({d '2000-09-13'}) returns September.
NOW()
Returns current date and time as a timestamp value. CONNX returns a value that is accurate to the millisecond.
Example: SELECT NOW() returns current date and time as a timestamp value in mm/dd/yy hh:mm:ss AM/PM format.
QUARTER(date_exp)
Returns the quarter in date_exp as an integer value in the range of 1-4, where 1 represents January 1 through March 31.
Example: SELECT QUARTER({d '2001-09-13'}) returns 3.
SECOND(time_exp)
Returns the second based on the second field in time_exp as an integer value in the range of 0-59.
Example: SELECT SECOND({t '10:00:03'}) returns 3.
TIMESTAMPADD(interval, integer_exp, timestamp_exp)
Returns a timestamp offset by the specified interval. Interval can be one of the following values:
SQL_TSI_FRAC_SECOND (in billionths of a second)
SQL_TSI_SECOND
SQL_TSI_MINUTE
SQL_TSI_HOUR
SQL_TSI_DAY
SQL_TSI_WEEK
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_YEAR
Example: SELECT timestampadd(SQL_TSI_HOUR, 3, {ts '1998-01-11 10:00:00'}) returns 1998-01-11 13:00:00.
Example: SELECT timestampadd(SQL_TSI_DAY, -3, {ts '1998-01-11 10:00:00'}) returns 1998-01-08 10:00:00.
TIMESTAMPDIFF(interval, timestamp_exp1, timestamp_exp2)
Returns the integer number of intervals of type interval by which timestamp_exp2 is greater than timestamp_exp1. Valid intervals are described in the TIMESTAMPADD function.
Example: SELECT timestampdiff(SQL_TSI_MONTH, {ts '1998-01-11 10:00:00'}, {ts '1998-03-11 10:00:00'}) returns 2.
Example: SELECT timestampdiff( SQL_TSI_YEAR, {ts '2003-01-11 10:00:00'}, {ts '1998-01-11 10:00:00'}) returns -5.
WEEK(date_exp)
Returns the ISO week number (ISO8601:2000 Standard). All weeks begin on a Monday. Week 1 starts on Monday of the first week of the calendar year with a Thursday.
Under the ISO standard, a week always begins on a Monday, and ends on a Sunday. The first week of a year is that week which contains the first Thursday of the year, or, equivalently, contains January 4th.
While this provides some standardization, it can lead to unexpected results - namely that the first few days of a year may not be in week 1 at all. Instead, they will be in week 52 of the preceding year.
An ISO week number may be between 1 and 53. Under the ISO standard, week 1 will always have at least 4 days. If January 1st falls on a Friday, Saturday, or Sunday, the first few days of the year are defined as being in the last (52nd or 53rd) week of the previous year.
Since compliance with these standards is entirely voluntary, your business may or may not use the ISO definitions.
Example: SELECT WEEK({d '2006-11-19'}) returns 46.
WEEKE1(date_exp)
Excel WEEKNUM function with an optional second argument of 1 (default). Week 1 begins on January 1st; week two begins on the following Sunday.
Example: SELECT WEEKE1({d '2006-11-19'}) returns 47.
WEEKE2(date_exp)
Excel WEEKNUM function with an optional second argument of 2. Week 1 begins on January 1st; week two begins on the following Monday.
Example: SELECT WEEKE2({d '2006-11-19'}) returns 47.
WEEKS(date_exp)
Absolute Week Numbers. Returns the week of the year based on the week field in date_exp as an integer value in the range of 1-53. An absolute week number is the seven day period that a date falls within, based solely on the first day of the year, regardless of the day of the week.
Week 1 is always January 1st to January 7th; week 2 is always January 8th to January 14th, and so on. If the year begins on a Thursday, then each "week" is from Thursday to the following Wednesday.
The absolute week number will always be between 1 and 53. Week 53 will have either one or two days, depending on whether the year is a leap year. If the year is a not a leap year, week 53 will consist of one day: December 31st. If the year is a leap year, week 53 will consist of two days: December 30th and December 31st.
Example: SELECT WEEKS({d '2006-11-19'}) returns 47.
YEAR(date_exp)
Returns the year based on the year field in date_exp as an integer value. The range is data source-dependent.
Example: SELECT YEAR({d '2001-09-13'}) returns 2001.