Time and Date Functions

The table below lists the time and date included in the ODBC scalar function set that can be used with the BASIS ODBC Driver.

Arguments denoted as date_exp can be the name of a column (of SQL_DATE, Julian, or OEM date type) or a date literal (constant).

Function

Description

CURDATE()

Returns the current date as a standard SQL DATE type value. BBj language-specific note: When returning SQL DATE values to BBj through SQLFETCH, the string template used to define the record contains a type I(4) field containing the date value as a Julian number.

CURTIME()

Returns the current time as a standard SQL TIME type value. BBj language-specific note: When returning SQL TIME values to BBj through SQLFETCH, the string template used to define the record contains a type C(23) field containing the time value as a string in the format hh:mm:ss.

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.

Example

Select ship_date, Dayname(ship_date), Monthname(ship_date) from  order_header

The following three types of arguments are allowed in the Time/Date functions:

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