Section 10.10.  Date/Time Functions

10.10. Date/Time Functions Oracle implements a number of functions that are useful when working with datetime values. You've seen many of them used earlier in this chapter. We don't document them all here, but we do provide a list in Table 10-5 to help you become familiar with what's available. We encourage you to refer to the SQL Reference manual and read up on those functions that interest you. Avoid using Oracle's traditional date functions with the new TIMESTAMP types. Instead, use the new INTERVAL functionality whenever possible. Use date functions only with DATE values. Many of the functions in Table 10-5 accept DATE values as inputs. ADD_MONTHS is an example of one such function. You must be careful when you consider using such functions to operate on any of the new TIMESTAMP datatypes. While you can pass a TIMESTAMP value to one of these functions, Oracle implicitly and silently converts that value to a DATE. Only then does the function perform its operation. For example: DECLARE x TIMESTAMP WITH TIME ZONE; BEGIN x := SYSTIMESTAMP;   --Notice that x now specifies fractional seconds --AND a time zone. DBMS_OUTPUT.PUT_LINE(x);   --Modify x using one of the built-in date functions. x := LAST_DAY(x);   --We've now LOST our fractional seconds, and the --time zone has changed to our session time zone. DBMS_OUTPUT.PUT_LINE(x); END; The output is: 13-MAR-05 04.27.23.163826 PM -08:00 31-MAR-05 04.27.23.000000 PM -05:00 In this example, the variable x contained a TIMESTAMP WITH TIME ZONE value. That value was implicitly converted into a DATE when it was passed to LAST_DAY. Because DATEs hold neither fractional seconds nor time zone offsets, those parts of x's value were silently discarded. The result of LAST_DAY was assigned back to x, causing a second, implicit conversion, this time from DATE to TIMESTAMP WITH TIME ZONE. This second conversion picked up the session time zone, and that's why you see -05:00 as the time zone offset in the final value. This behavior is critical to understand! It's critical to avoid too. We're sure you can imagine the sort of subtle program errors that can be induced by careless application of DATE functions to TIMESTAMP values. Frankly, we can't imagine why Oracle did not overload the built-in DATE functions so that they also worked properly for TIMESTAMPs. Be careful! Table 10-5. Built-in datetime functions Name Description ADD_MONTHS Adds a specified number of months to a DATE. See the section "Adding and Subtracting Intervals to/from Datetimes" for an example. CAST Converts between datatypesfor example, between DATE and the various TIMESTAMP datatypes. See the section "CAST and EXTRACT." CURRENT_DATE Returns the current date and time as a DATE value in the session time zone. CURRENT_TIMESTAMP Returns the current date and time as a TIMESTAMP WITH TIME ZONE value in the session time zone. DBTIMEZONE Returns the time zone offset (from UTC) of the database time zone in the form of a character string (e.g., '-05:00'). EXTRACT Returns the value of a specific datetime element, such as the hour, the minute, the day, the year, etc. See the section "CAST and EXTRACT." FROM_TZ Adds time zone information to a TIMESTAMP value, converting it to a TIMESTAMP WITH TIME ZONE. LAST_DAY Returns the last day in the month containing a specified input DATE. LOCALTIMESTAMP Returns the current date and time as a TIMESTAMP value in the local time zone. MONTHS_ BETWEEN Calculates the number of months between two DATEs. See the section"Computing the Interval Between Two Datetimes" for an example. NEW_TIME Shifts a DATE value from one time zone to another. This functionality is somewhat obsolete. If you need to work with time zones in new applications, use the TIMESTAMP WITH TIME ZONE type. NEXT_DAY Returns the DATE of the first weekday specified that is later than an input DATE. NUMTODSINTERVAL Converts a number representing some number of days, hours, minutes, or seconds (your choice) to a value of type INTERVAL DAY TO SECOND. NUMTOYMINTERAL Converts a number representing some number of years or months (your choice) to a value of type INTERVAL YEAR TO MONTH. ROUND Returns a DATE rounded to a specified level of granularity. SESSIONTIMEZONE Returns the time zone offset (from UTC) of the session time zone in the form of a character string (e.g., '-05:00'). SYSDATE Returns the current date and time from the Oracle server as a DATE value. SYS_EXTRACT_UTC Converts a TIMESTAMP WITH TIME ZONE value to a TIMESTAMP having the same date and time, but normalized to UTC. SYSTIMESTAMP Returns the current date and time from the Oracle server as a TIMESTAMP WITH TIME ZONE value. TO_CHAR Converts datetime values to their character string representations. See the section "Datetime Conversions." TO_DATE Converts the character string representation of a date and time to a value of type DATE. See the section "Datetime Conversions." TO_DSINTERVAL Converts the character string representation of an interval expressed in days, hours, minutes, and seconds to a value of INTERVAL DAY TO SECOND. See the section "Interval Conversions." TO_TIMESTAMP Converts the character string representation of a date and time to a value of type TIMESTAMP. See the section "Datetime Conversions." TO_TIMESTAMP_TZ Converts the character string representation of a date and time to a value of type TIMESTAMP WITH TIME ZONE. See the section "Datetime Conversions." TO_YMINTERVAL Converts the character string representation of an interval expressed in years and months to a value of INTERVAL YEAR TO MONTH. See the section "Interval Conversions." TRUNC Truncates a DATE value to a specified level of granularity. TZ_OFFSET Returns the time zone offset from UTC (e.g., '-05:00') for a given time zone name, abbreviation, or offset.