Oracle PL/SQL Programming, 5th Edition

Date/Time Function Quick Reference

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. I don’t document them all here, but I do provide a list in Table 10-5 to help you become familiar with what’s available. I encourage you to refer to Oracle’s SQL Reference manual and read up on those functions that interest you.

Warning

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, the database implicitly and silently converts that value to a DATE. Only then does the function perform its operation. For example:

DECLARE
   ts TIMESTAMP WITH TIME ZONE;
BEGIN
   ts := SYSTIMESTAMP;

   --Notice that ts now specifies fractional seconds
   --AND a time zone.
   DBMS_OUTPUT.PUT_LINE(ts);

   --Modify ts using one of the built-in date functions.
   ts := LAST_DAY(ts);

   --We've now LOST our fractional seconds, and the
   --time zone has changed to our session time zone.
   DBMS_OUTPUT.PUT_LINE(ts);
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 ts contained ...