Sql – Oracle SQL – DATE greater than statement

dateformatoraclesql

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.

Best Answer

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');
Related Topic