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 series of images below shows you two queries and the results from each query.
Date Comparison Series of Images
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.
The following page discusses the comparison of dates in Oracle.
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.
Oracle Functions Returned Values
The following paragraphs display a sample function with its corresponding result.
Both the header and the body of the function are created by this command. The return keyword specifies the datatype of the function's return value. This can be any valid PL/SQL datatype. Every function must have a return clause, since the function must, by definition, return a value to the calling environment. The following example shows a function named OVERDUE_CHARGES, which returns the overdue book charges by person, based on calculations against the BOOKSHELF_CHECKOUT table.
The input is the name of the person, while the output is the balance for that person.