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