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.

'DATE' stored as that, and I can't convert them using to_date — user3521826, May 31 '15 at 10:03

Sylvain Leroux · Answer 1 · 2015-05-31T09:30:46.303

108

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

edited May 31 '15 at 09:30

answered May 31 '15 at 09:24

Sylvain Leroux

50,096
7
103
125

Thank you, I stumbled on your answer and it gives me the right answer. However could you please explain why `WHERE created >= Convert(datetime, '2014-04-01')` doesn't work? Says Syntax error. – Nicholas Humphrey Aug 17 '18 at 14:12
2

@NicholasHumphrey Convert is not related to datetime conversion, rather it is used for converting between character sets (Latin-1 to ASCII etc.) – macleay Dec 06 '18 at 23:31

score 39 · Answer 2 · edited May 08 '18 at 12:18

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

score 1 · Answer 3 · answered May 31 '15 at 09:22

1

you have to use the To_Date() function to convert the string to date ! http://www.techonthenet.com/oracle/functions/to_date.php

answered May 31 '15 at 09:22

kamokaze

7,142
5
33
43

Oracle SQL - DATE greater than statement

3 Answers3