Time and Date Functions

Function

Description

CURDATE()

Returns the current date in the format yyyy-mm-dd, as a DATE.

CURTIME()

Returns the current time as a CHAR.

DAYNAME(date_exp)

Returns a character string containing the name of the day of the week (e.g., Sunday) for the day portion of date_exp.

DAYOFMONTH(date_exp)

Returns the day of the month in date_exp as an integer value in the range of 1-31.

DAYOFWEEK(date_exp)

Returns the day of the week 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 in date_exp as an integer value in the range of 1-366.

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 in date_exp as an integer value in the range of 1-12.

MONTHNAME(date_exp)

Returns a character string containing the name of the month (e.g., January through December) for the month portion of date_exp.

NOW()

Returns the current date and time as a timestamp value.

QUARTER(date_exp)

Returns the quarter in date_exp as an integer value in 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 the timestamp calculated by adding integer_exp intervals of type interval to timestamp_exp.

Possible values for the interval are: SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER or SQL_TSI_YEAR

TO_DATE(date_str, format)

In BBj 15.0 and higher, converts the specified date string to an SQL DATE value using the optionally provided format string. If no format is specified it uses "yyyy-MM-dd". The format is extremely powerful and follows the formatting rules of the Java class java.text.SimpleDateFormat.

TO_TIMESTAMP(timestamp_str, format)

In BBj 15.0 and higher, converts the specified timestamp string to an SQL TIMESTAMP value using the optionally provided format string. If no format is specified it uses "yyyy-MM-dd HH:mm:ss.SSS". The format is extremely powerful and follows the formatting rules of the Java class java.text.SimpleDateFormat.

WEEK(date_exp)

Returns the week as an integer value in range 1-52, where '1998-01-16' would return 3.

YEAR(date_exp)

Returns the year in date_exp as an integer value.

CURTIME() or Time Constant

OEM/Julian Date or Date Constant or CURDATE()

Timestamp field or NOW()

HOUR

Y

Y

MINUTE

Y

Y

SECOND

Y

Y

YEAR

Y

Y

MONTH

Y

Y

DAYOFWEEK

Y

Y

QUARTER

Y

Y

DAYNAME

Y

Y

DAYOFMONTH

Y

Y

DAYOFYEAR

Y

Y

MONTHNAME

Y

Y