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 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.
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.
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.
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
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.

  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.

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 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.
Date and time formats 
SAMPLE_ONE 
--------------------
TO_CHAR(SYSDATE,'MM/DD/YYYY HH:M|p.m.')


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


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


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

Oracle Functions Returned Values

The following paragraphs display a sample function with its corresponding result.

Oracle Functions Returned Values

  1. TO_CHAR(BIRTH_DATE,'yy/dd HH24:MI') , 67/05 07:30
  2. ROUND(TRUNC(LENGTH(FIRSTNAME)+BANK_BALANCE,1),0), 1249
  3. TO_CHAR(BIRTH_DATE+5,'DD') || '/' ||
TO_CHAR(LAST_DAY(BIRTH_DATE),'DD') ||
TO_CHAR( BIRTH_DATE,' HH:MI')
    , 10/30 07:30
  4. FLOOR(BANK_BALANCE/100)*100+INTEREST_RATE*1000 , 1245.1
  5. SIGN(TO_DATE('01-JAN-75') - BIRTH_DATE) * TRUNC(BANK_BALANCE,-2), 1200

Create function Syntax

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]...
)]
return datatype
[{ invoker_rights_clause | deterministic | parallel_enable_clause }
[ invoker_rights_clause | deterministic | parallel_enable_clause ]...
]
{ { aggregate | pipelined } using [schema .] implementation_type
| [pipelined] { is | as } { pl/sql_function_body | call_spec }};

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.

Ad Oracle Database SQL