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 parameter
Description
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 series of images below shows you two queries and the results from each query.
Date Comparison Series of Images
1) The query result lists one row and displays the LAST_UPDATE_DATETIME as 12-DEC-99. This means there is one row in which the LAST_UPDATE_DATETIME lies between midnight December 12 and midnight on December 13. Midnight is the default time assigned to dates where no time is specified, such as the literal dates in the WHERE clause.
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. As you can see, the time is 5:30 PM.
3) We have modified the query to look for LAST_UPDATE_DATETIME equal to December 12. Since the literal date has the default time of midnight, and the time in the December 12 row is 5:30 PM, no rows satisfy the query.
4) Tell SQL to ignore the time included in a date column using the TRUNC function. In this example, the query has been modified so that the time stored in the database column is ignored, causing the query to return a row even though the times are not equal.
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.
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 unit
Factor 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.
The syntax for the create function command is more complicated than the syntax for the create procedure command.
At a high level, the syntax is
create [or replace] function [schema .] function
[( argument [ in | out | in out ] [nocopy] datatype
[, argument [ in | out | in out ] [nocopy] datatype]...
)]
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.