SQL Date Functions

CURRENT_DATE ( )

Returns the current date.

CURRENT_TIME ()

Returns the current local time.

CURRENT_TIMESTAMP ()

Returns the current local date and local time as a timestamp value.

CURDATE ( )

Returns the current date.

CURTIME ( )

Returns the current local time.

DAYNAME (date_exp)

Returns a character string containing the data source/specific name of the day (for example, Sunday through Saturday).

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.

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.

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.

EXTRACT (extract-field FROM extract-source)

Returns the extract-field portion of the extract-source. The extract-source argument is a datetime or interval expression.

HOUR (time_exp)

Returns the hour based on the hour field in time_exp as an integer value in the range of 0-23.

MINUTE (time_exp)

Returns the minute based on the minute field in time_exp as an integer value in the range of 0-59.

MONTH (date_exp)

Returns the month based on the month field in date_exp as an integer value in the range of 1-12.

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.

NOW ( )

Returns current date and time as a timestamp value.

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.

SECOND (time_exp)

Returns the second based on the second field in time_exp as an integer value in the range of 0-59.

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-01 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 week of the year based on the week field in date_exp as an integer value in the range of 1-53.

YEAR(date_exp)

Returns the year based on the year field in date_exp as an integer value. The range is data source-dependent.