Oracle SQL - DATE greater than statement

Question

As the title says, I want to find a way to check which of my data sets are past 6 months from SYSDATE via query.

SELECT * FROM OrderArchive WHERE OrderDate <= '31 Dec 2014'; 

I've tried the following but it returns an error saying my date format is wrong. However, inserting the data I used that date format as requested/intended and had no issues.

Error at Command Line : 10 Column : 25

Blockquote

Error report -

SQL Error: ORA-01861: literal does not match format string 01861. 00000 - "literal does not match format string"

*Cause: Literals in the input must be the same length as literals in the format string (with the exception of leading whitespace). If the "FX" modifier has been toggled on, the literal must match exactly, with no extra whitespace.

*Action: Correct the format string to match the literal.

Answer 1

As your query string is a literal, and assuming your dates are properly stored as DATE you should use date literals:

SELECT * FROM OrderArchive WHERE OrderDate <= DATE '2015-12-31' 

If you want to use TO_DATE (because, for example, your query value is not a literal), I suggest you to explicitly set the NLS_DATE_LANGUAGE parameter as you are using US abbreviated month names. That way, it won't break on some localized Oracle Installation:

SELECT * FROM OrderArchive WHERE OrderDate <= to_date('31 Dec 2014', 'DD MON YYYY',                            'NLS_DATE_LANGUAGE = American'); 

Answer 2

You need to convert the string to date using the to_date() function

SELECT * FROM OrderArchive WHERE OrderDate <= to_date('31-Dec-2014','DD-MON-YYYY'); 

OR

SELECT * FROM OrderArchive WHERE OrderDate <= to_date('31 Dec 2014','DD MON YYYY'); 

OR

SELECT * FROM OrderArchive WHERE OrderDate <= to_date('2014-12-31','yyyy-MM-dd'); 

This will work only if OrderDate is stored in Date format. If it is Varchar you should apply to_date() func on that column also like

 SELECT * FROM OrderArchive     WHERE to_date(OrderDate,'yyyy-Mm-dd') <= to_date('2014-12-31','yyyy-MM-dd');