RelationalDBDesignRelationalDBDesign



Data Manipulation   «Prev 

Date comparison using Oracle SQL Extensions

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 wrong 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:
TO_CHAR(date[,'format'[,'NLSparameters']])
TO_DATE(string[,'format'[,'NLSparameters']])

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.

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.

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.

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.

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.