Data Manipulation   «Prev  Next»

Lesson 7 Date/time functions
Objective Identify valid time-related date/time functions.

Oracle Date and Time Functions

The Oracle date datatype always stores both date and time. If you insert or update a date in your database without specifying the time, Oracle defaults the time to midnight of that day.
There is a whole set of formats for times when you use the TO_CHAR function, just like there are formats for dates. Here is a quick list:

Format parameterDescription
HH or HH12 Hours (12 hour clock)
HH24 Hours (24 hour clock)
PM Add AM or PM to the time
MI Minutes
SS Seconds
SSSSS Seconds since midnight

Date comparison

It is important to understand that the default date comparison between two columns compares both date and time. This is usually no problem, but occasionally, you might want to compare dates only and ignore the time. Let us look at an example from the pet store schema. The SlideShow below shows you two queries and the results from each query.


  1. The query result lists one row and displays the LAST_UPDATE_DATETIME as 12-DEC-99.
  2. To verify that the time in the December 12 row is really not midnight, the first query is repeated here, except we have added the TO_CHAR function so that we see the date and the time displayed.
  3. We have modified the query to look for LAST_UPDATE_DATETIME equal to December 12.
  4. Tell SQL to ignore the time included in a date column using the TRUNC function.

Date Comparison

Time arithmetic

You can add or subtract hours, minutes, or seconds to a date. Simply use the date math (as seen in the previous lesson) and convert the time into a fraction of a day. The table below shows you a quick conversion chart.
Time unitFactor to convert to fraction of a day
Hour .0416666
Minute .0006944
Second .0000115

Here is an example where you add eight hours to a date and time:
SELECT 
TO_CHAR(P.LAST_UPDATE_DATETIME,
        'DD-MON-YY HH:MI PM') ORIG,
TO_CHAR(P.LAST_UPDATE_DATETIME+(8*.0416666),
        'DD-MON-YY HH:MI PM') LATER
FROM PET_CARE_LOG P

The results when you execute the query are:
ORIG               LATER
------------------ ------------------
19-DEC-99 07:45 AM 19-DEC-99 03:45 PM
12-DEC-99 05:30 PM 13-DEC-99 01:30 AM
16-JAN-00 01:30 PM 16-JAN-00 09:30 PM

The following graphic shows examples of date and time formats you can use.
SAMPLE_ONE 
--------------------
TO_CHAR(SYSDATE,'MM/DD/YYYY HH:M|p.m.')

SAMPLE_TWO 
--------------------

SAMPLE_THREE 
--------------------

SAMPLE_FOUR
--------------------

In the next lesson, you will learn to modify query results using the DECODE and NVL functions.

Oracle Functions Returned Values

Click on the Exercise link below to match a sample function with its result.
Oracle Functions Returned Values