Table of Contents Previous Next


2 The SQL Language : 2.4 Functions and Operators : 2.4.8 Date/Time Functions and Operators

Table 2‑25 shows the available functions for date/time value processing, with details appearing in the following subsections. The following table illustrates the behaviors of the basic arithmetic operators (+, -). For formatting functions, refer to Section 2.4.7. You should be familiar with the background information on date/time data types from Section 2.2.4.
In the date/time functions of the following table the use of the DATE and TIMESTAMP data types are interchangeable.
EXTRACT(field FROM TIMESTAMP)
LOCALTIMESTAMP [ (precision) ]
NEXT_DAY(DATE, dayofweek)
Date falling on dayofweek following specified date; see Section 2.4.8.4
TRUNC(DATE [format])
2.4.8.1 ADD_MONTHS
The ADD_MONTHS functions adds (or subtracts if the second parameter is negative) the specified number of months to the given date. The resulting day of the month is the same as the day of the month of the given date except when the day is the last day of the month in which case the resulting date always falls on the last day of the month.
2.4.8.2 EXTRACT
The EXTRACT function retrieves subfields such as year or hour from date/time values. The EXTRACT function returns values of type DOUBLE PRECISION. The following are valid field names:
The MONTHS_BETWEEN function returns the number of months between two dates. The result is a numeric value which is positive if the first date is greater than the second date or negative if the first date is less than the second date.
2.4.8.4 NEXT_DAY
The NEXT_DAY function returns the first occurrence of the given weekday strictly greater than the given date. At least the first three letters of the weekday must be specified - e.g., SAT. If the given date contains a time portion, it is carried forward to the result unchanged.
2.4.8.5 NEW_TIME
The NEW_TIME function converts a date and time from one time zone to another. NEW_TIME returns a value of type DATE. The syntax is:
NEW_TIME(DATE, time_zone1, time_zone2)
time_zone1 and time_zone2 must be string values from the Time Zone column of the following table:
2.4.8.6 ROUND
The ROUND function returns a date rounded according to a specified template pattern. If the template pattern is omitted, the date is rounded to the nearest day. The following table shows the template patterns for the ROUND function.
Returns January 1, cc01 where cc is first 2 digits of the given year if last 2 digits <= 50, or 1 greater than the first 2 digits of the given year if last 2 digits > 50; (for AD years)
Returns January 1, yyyy where yyyy is rounded to the nearest year; rounds down on June 30, rounds up on July 1
The following examples round to the nearest ISO year. The first example rounds to 2004 and the ISO year for 2004 begins on December 29th of 2003. The second example rounds to 2005 and the ISO year for 2005 begins on January 3rd of that same year.
The following examples round to the nearest week. The first day of 2007 lands on a Monday so in the first example, January 18th is closest to the Monday that lands on January 15th. In the second example, January 19th is closer to the Monday that falls on January 22nd.
2.4.8.7 TRUNC
The TRUNC function returns a date truncated according to a specified template pattern. If the template pattern is omitted, the date is truncated to the nearest day. The following table shows the template patterns for the TRUNC function.
Returns January 1, cc01 where cc is first 2 digits of the given year
Returns January 1, yyyy where yyyy is the given year
Advanced Server provides a number of functions that return values related to the current date and time. These functions all return values based on the start time of the current transaction.
CURRENT_DATE returns the current date and time based on the start time of the current transaction. The value of CURRENT_DATE will not change if called multiple times within a transaction.
CURRENT_TIMESTAMP returns the current date and time. When called from a single SQL statement, it will return the same value for each occurrence within the statement. If called from multiple statements within a transaction, may return different values for each occurrence. If called from a function, may return a different value than the value returned by current_timestamp in the caller.
LOCALTIMESTAMP can optionally be given a precision parameter which causes the result to be rounded to that many fractional digits in the seconds field. Without a precision parameter, the result is given to the full available precision.
The NUMTODSINTERVAL function converts a numeric value to a time interval that includes day through second interval units. When calling the function, specify the smallest fractional interval type to be included in the result set. The valid interval types are DAY, HOUR, MINUTE, and SECOND.
The NUMTOYMINTERVAL function converts a numeric value to a time interval that includes year through month interval units. When calling the function, specify the smallest fractional interval type to be included in the result set. The valid interval types are YEAR and MONTH.

2 The SQL Language : 2.4 Functions and Operators : 2.4.8 Date/Time Functions and Operators

Table of Contents Previous Next