Oracle between query on string field

Question

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 · Accepted Answer · 2014-12-19 09:45:16Z

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

edited Dec 19, 2014 at 9:45

answered Dec 19, 2014 at 9:29

Sylvain Leroux

52.3k8 gold badges114 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Falco Over a year ago

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

Sylvain Leroux Over a year ago

Thank you for your comment, @Falco ! I took the liberty to add that to my answer. Please fell free to revert if you desagree.

Suganthan Madhavan Pillai Over a year ago

@Falco Sometimes in the same column normal string values would also come

Falco Over a year ago

@Suganthan Then you should create a function based index which returns NULL for ROWs which do not contain a valid date. See also: stackoverflow.com/questions/5966274/…

Raptor · Accepted Answer · 2014-12-19 09:36:32Z

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.

edited Dec 19, 2014 at 9:36

answered Dec 19, 2014 at 9:29

Raptor

54.4k47 gold badges254 silver badges405 bronze badges

1 Comment

Suganthan Madhavan Pillai Over a year ago

Sorry. the column name is not value that is just a test column name. I 'll update my question

Collectives™ on Stack Overflow

Oracle between query on string field

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related