Call Level Interface Guide and Reference

Date and Time Functions

The date and time functions in this section are supported by DB2 CLI and defined by ODBC using vendor escape clauses.

• Arguments denoted as timestamp_exp can be the name of a column, the result of another scalar function, or a time,date, or timestamp literal.
• Arguments denoted as date_exp can be the name of a column, the result of another scalar function, or a date or timestamp literal, where the underlying data type can be character based, or date or timestamp based.
• Arguments denoted as time_exp can be the name of a column, the result of another scalar function, or a time or timestamp literal, where the underlying data types can be character based, or time or timestamp based.

Table 191. Date and Time Scalar Functions

CURDATE() Returns the current date as a date value.
DB2 for	common server 1.0	MVS	VM/VSE	AS/400
CURTIME() Returns the current local time as a time value.
DB2 for	common server 1.0	MVS	VM/VSE	AS/400
DAYNAME( date_exp ) Returns a character string containing the name of the day (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday ) for the day portion of date_exp.
DB2 for	common server 2.1
DAYOFMONTH ( date_exp ) Returns the day of the month in date_exp as an integer value in the range of 1-31.
DB2 for	common server 1.0	MVS	VM/VSE	AS/400
DAYOFWEEK( date_exp ) Returns the day of the week in date_exp as an integer value in the range 1-7, where 1 represents Sunday.
DB2 for	common server 2.1			AS/400 3.6
DAYOFYEAR( date_exp ) Returns the day of the year in date_exp as an integer value in the range 1-366.
DB2 for	common server 2.1			AS/400 3.6
HOUR( time_exp ) Returns the hour in time_exp as an integer value in the range of 0-23.
DB2 for	common server 1.0	MVS	VM/VSE	AS/400
JULIAN_DAY( date_exp ) Returns the number of days between date_exp and January 1, 4712 B.C. (the start of the Julian date calendar). (When moving from a v2.1.0 to a v2.1.1 database you must run the migrate utility to access this function.)
DB2 for	common server 2.1.1
MINUTE( time_exp ) Returns the minute in time_exp as integer value in the range of 0-59.
DB2 for	common server 1.0	MVS	VM/VSE	AS/400
MONTH( date_exp ) Returns the month in date_exp as an integer value in the range of 1-12.
DB2 for	common server 1.0	MVS	VM/VSE	AS/400
MONTHNAME( date_exp ) Returns a character string containing the name of month (January, February, March, April, May, June, July, August, September, October, November, December) for the month portion of date_exp.
DB2 for	common server 2.1
NOW() Returns the current date and time as a timestamp value.
DB2 for	common server 1.0	MVS	VM/VSE	AS/400
QUARTER( date_exp ) Returns the quarter in date_exp as an integer value in the range of 1-4.
DB2 for	common server 2.1			AS/400 3.6
SECOND( time_exp ) Returns the second in time_exp as an integer value in the range of 0-59.
DB2 for	common server 1.0	MVS	VM/VSE	AS/400
SECONDS_SINCE_MIDNIGHT( time_exp ) Returns the number of seconds in time_exp relative to midnight as an integer value in the range of 0-86400. If time_exp includes a fractional seconds component, the fractional seconds component will be discarded. (When moving from a v2.1.0 to a v2.1.1 database you must run the migrate utility to access this function.)
DB2 for	common server 2.1.1
TIMESTAMPADD( interval, integer_exp, timestamp_exp ) Returns the timestamp calculated by adding integer_exp intervals of type interval to timestamp_exp. Valid values of 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 SQL_TSI_YEAR where fractional seconds are expressed in billionths of a second. If timestamp_exp specifies a time value and interval specifies days, weeks, months, quarters, or years, the date portion of timestamp_exp is set to the current date before calculating the resulting timestamp. If timestamp_exp is a date value and interval specifies fractional seconds, seconds, minutes, or hours, the time portion of timestamp_exp is set to 00:00:00.000000 before calculating the resulting timestamp. An application determines which intervals are supported by calling SQLGetInfo() with the SQL_TIMEDATE_ADD_INTERVALS option.
DB2 for	common server 2.1
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 values of 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 SQL_TSI_YEAR where fractional seconds are expressed in billionths of a second. If either timestamp expression is a time value and interval specifies days, weeks, months, quarters, or years, the date portion of that timestamp is set to the current date before calculating the difference between the timestamps. If either timestamp expression is a date value and interval specifies fractional seconds, seconds, minutes, or hours, the time portion of that timestamp is set to 0 before calculating the difference between the timestamps. An application determines which intervals are supported by calling SQLGetInfo() with the SQL_TIMEDATE_DIFF_INTERVALS option.
DB2 for	common server 2.1
WEEK( date_exp ) Returns the week of the year in date_exp as an integer value in the range of 1-54.
DB2 for	common server 2.1			AS/400 3.6
YEAR( date_exp ) Returns the year in date_exp as an integer value in the range of 1-9999.
DB2 for	common server 1.0	MVS	VM/VSE	AS/400

For those functions that return a character string containing the name of the day of week or the name of the month, these character strings will be National Language Support enabled.