0

I having a table like this

---------------------------
|id |condition_values     |
---------------------------
|1  |2012-05-12           |
|2  |2012-06-12           |
|3  |2012-07-12           |
|4  |2012-08-12           |
---------------------------

So when I trying to query like

select * from tableName where condition_values >= TO_DATE('2012-05-12', 'yyyy-mm-dd')
AND condition_values <= TO_DATE('2012-07-12','yyyy-mm-dd');

I getting SQL Error

ORA-01861: literal does not match format string

Note: value is a VARCHAR2 field

Sylvain Leroux
  • 50,096
  • 7
  • 103
  • 125
Suganthan Madhavan Pillai
  • 5,495
  • 9
  • 51
  • 84

2 Answers2

3
select * from tableName where "value" >= TO_DATE('2012-05-12', 'yyyy-mm-dd') AND "value" <= TO_DATE('2012-07-12','yyyy-mm-dd');
--                             ^^^^^

You have an implicit type conversion here using you NLS date format, as, according to the documentation (same link):

When comparing a character value with a DATE value, Oracle converts the character data to DATE.

The quick fix is to write:

select * from tableName where TO_DATE("value", 'yyyy-mm-dd') >= TO_DATE('2012-05-12', 'yyyy-mm-dd') 
                           AND TO_DATE("value", 'yyyy-mm-dd') <= TO_DATE('2012-07-12','yyyy-mm-dd');

But, as your date format is lexicography comparable, you might write that instead:

select * from tableName where "value" >= '2012-05-12' 
                           AND "value" <= '2012-07-12';

However, I would strongly suggest you to fix your data to ensure that you use the proper DATE type for your column. This will avoid a bunch of possible bugs and problems like this one.

As suggested by Falco in a comment below, "if you cannot change the column type to DATE, you should probably create a function-based Index for the TO_DATE(...) Value to get fast query times".

Sylvain Leroux
  • 50,096
  • 7
  • 103
  • 125
  • 2
    If you cannot change the column type to date, you should probably create a function-based Index for the TO_DATE(...) Value to get fast query times! - you could then use the index with a view - so no extra coding is required. But saving the initial data as DATE would be the best option – Falco Dec 19 '14 at 09:38
  • Thank you for your comment, @Falco ! I took the liberty to add that to my answer. Please fell free to revert if you desagree. – Sylvain Leroux Dec 19 '14 at 09:42
  • @Falco Sometimes in the same column normal string values would also come – Suganthan Madhavan Pillai Dec 19 '14 at 09:46
  • 1
    @Suganthan Then you should create a function based index which returns NULL for ROWs which do not contain a valid date. See also: http://stackoverflow.com/questions/5966274/how-to-handle-to-date-exceptions-in-a-select-statment-to-ignore-those-rows – Falco Dec 19 '14 at 09:48
1

VALUE is a reserved word. Either change the column name or:

select * from tableName 
where `value` >= TO_DATE('2012-05-12', 'yyyy-mm-dd')
AND value <= TO_DATE('2012-07-12','yyyy-mm-dd');

Reference: Reserved Words in Oracle

If I were you, I will avoid using reserved words in column names.

OP has updated the question. It is recommended to use DATE column type instead of VARCHAR2 to avoid TO_DATE() conversion. Faster querying, Less problem.

Raptor
  • 53,206
  • 45
  • 230
  • 366