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 following series of images 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. 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 following section discusses the comparison of dates in Oracle.
How to compare dates in Oracle
In Oracle, the best way to compare dates is to use the built-in DATE datatype and the comparison operators. The DATE datatype in Oracle stores both date and time information, making it ideal for storing and manipulating date and time values. Here are some ways to compare dates in Oracle:
Comparison operators: The comparison operators (<, <=, >, >=, =, and !=) can be used to compare dates in Oracle. For example, to find all records where the date is greater than or equal to a certain date, you can use a query like this:
SELECT * FROM mytable
WHERE mydate >= '01-JAN-2022';
Date functions: Oracle provides several built-in date functions that can be used to manipulate and compare dates. For example, the TO_DATE function can be used to convert a string to a date datatype, and the TRUNC function can be used to truncate the time component of a date value. For example, to find all records where the date is greater than or equal to a certain date, truncated to the day, you can use a query like this:
SELECT * FROM mytable
WHERE TRUNC(mydate) >= TO_DATE('01-JAN-2022', 'DD-MON-YYYY');
Interval functions: Oracle provides several built-in interval functions that can be used to manipulate and compare date intervals. For example, the MONTHS_BETWEEN function can be used to find the number of months between two dates, and the ADD_MONTHS function can be used to add or subtract months from a date. For example, to find all records where the date is within a certain number of months from a given date, you can use a query like this:
SELECT * FROM mytable
WHERE ABS(MONTHS_BETWEEN(mydate, '01-JAN-2022')) <= 6;
Question: How do I compare dates in Oracle? Answer: In Oracle, dates have a DATE internal format and comparing DATES should be with matching data types,
preferably a DATE to DATE datatype. If you avoid this data type mismatch and convert your text with the to_date function it is easy to compare dates in Oracle;
where my_date_col > to_date(2011-10-13,YYYY-MM-DD);
Below we compare to DATE in character format:
where to_char(my_char_col) > 2011-10-13
Comparing Dates the incorrect Way
These Oracle date comparisons works, but no index cannot be used because date2 is invalidated with the trunc function
(unless you create a function-based index on trunc(date2,'YYYY:MM').
where to_char(DATE1,'YYYY:MM') >= to_char(DATE2,'YYYY:MM')
where trunc(date1,'mm') >= trunc(date2,'mm');
TO_DATE and TO_CHAR Formatting
TO_DATE and TO_CHAR are alike insofar as they both have powerful formatting capabilities.They are opposite insofar as TO_DATE converts a character string or a number into an Oracle date, whereas TO_CHAR converts an Oracle date into a character string. The formats for these two functions are as follows:
date must be a column defined as a DATE datatype in Oracle. It cannot be a string, even if it is in the most common date format of DD-MON-YY.
The only way to use a string where date appears in the TO_CHAR function is to enclose it within a TO_DATE function.
string is a 1) literal string, 2) literal number, or 3) database column containing a string or a number.
In every case but one, the format of string must correspond to that described by format. Only if a string is in the default format can format be left out. The default starts out as "DD-MON-YY", but you can change this with
alter session set NLS_DATE_FORMAT = "DD/MON/YYYY";
for a given SQL session or with the NLS_DATE_FORMAT init.ora parameter. format is a collection of many options that can be combined in virtually an infinite number of ways. Once you understand the basic method of using the options, putting them into practice is simple. NLSparameters is a string that sets the NLS_DATE_LANGUAGE option to a specific language, as opposed to using the language for the current SQL session. You should not need to use this option often. Oracle will return day and month names in the language set for the session with alter session.
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.
