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:
HH or HH12
Hours (12 hour clock)
Hours (24 hour clock)
Add AM or PM to the time
Seconds since midnight
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.
The query result lists one row and displays the LAST_UPDATE_DATETIME as 12-DEC-99.
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.
We have modified the query to look for LAST_UPDATE_DATETIME equal to December 12.
Tell SQL to ignore the time included in a date column using the TRUNC function.
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.
Factor to convert to fraction of a day
Here is an example where you add eight hours to a date and time:
'DD-MON-YY HH:MI PM') ORIG,
'DD-MON-YY HH:MI PM') LATER
FROM PET_CARE_LOG P
The results when you execute the query are:
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.
In the next lesson, you will learn to modify query results using the DECODE and NVL functions.