SQL — Time and Date Functions

CURDATE(date_exp)

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(time_exp)

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.

CURRENT_DATE()

CURRENT_TIME()

CURRENT_TIMESTAMP()

DATE(num1{,num2}{:str}{,ERR=lineref})

DATEADD(interval, number, date_val)

Adds a specified number of units (e.g., days, months, years) to a given date and returns a new SQL_DATE value.

Add 2 Months: SELECT DATEADD(SQL_TSI_MONTH, 2,'2024-10-13)

Add 2 Weeks :SELECT DATEADD(SQL_TSI_WEEK, 2, '2024-10-13')

It Supports: 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, SQL_TSI_YEAR

Returns the difference between two dates in the specified unit (e.g., days, months, years).

Valid Units: day, month, year, hour, minute, second, millisecond, SELECT DATEADD('1971-10-13', NOW(), 'years')

JUL(year,month,day{MODE="SERVER|CLIENT"}{,ERR=lineref})

JUL(str1{,str2}{,str3}{,ERR=lineref})

TIME_TO_MIN(date_or_time_val)

TIME_TO_SEC(date_or_time_val)

TO_DATE(date_as_string, optional_format)

TO_TIMESTAMP(timestamp_as_string, optional_format)

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.

DAYSINMONTH(date_exp)

DAYSINYEAR(date_exp)

Returns the number of days in the year specified. Function takes a standard date or date string, but it also accepts a full four digit year as a numeric or string value. Function automatically takes into account leap years.

HOUR(time_exp)

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

ISDATE(char_exp)

Returns 1 if the parameter value can be parsed as a date value in the standard SQL DATE format of yyyy-mm-dd. Returns 0 if it is not in this format. This function will also return 1 for values in the standard TIMESTAMP format yyyy-mm-dd hh:MM:ss. If the parameter value is a DATE type expression, it will always return 1.

ISNUMERIC(char_exp)

Returns 1 if the parameter value can be parsed as a number or 0 if not. This accepts digits, decimal point, and sign characters. If the parameter is a numeric type expression (INTEGER, FLOAT, DECIMAL, etc.) it will always return 1.

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.

USE_DATE_FORMAT(raw_data, bbj_date_format)

In BBj 11.0 and higher, this function uses the specified bbj_date_format to process the data in raw_data, returning a valid SQL DATE or TIMESTAMP value depending on the type of date format. Valid values (case sensitive) for bbj_date_format include: ADP, AON, AON3_CYYMMDD, CITI, CYYMMDD, DayMonthYear, DISC, Julian, Julian_Time, MAI, Millisecond, MonthDayYear, SOA, SQLDate, SSI, SSIJ and YMD8.

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.