Varchar2 to Date for sysdate comparison

Question

A table I am querying has dates stored as varchar2. I need to compare the dates stored as varchar2 with sysdate using BETWEEN sysdate-1 AND sysdate-30 (to return varchar2 dates from the last month).

When specifying TO_DATE(varchar2, 'DD-MON-YYYY') I get error "literal does not match format string".

I am stuck as Oracle documentation says this is an acceptable format for TO_DATE() when converting from varchar2.

UPDATE: This is a corporate database, I did not design the DB and can only work with what I have available. The data set is enormous and is automatically updated by SCADA devices, over 10,000 devices daily.

SELECT device_name, read_date, sysdate

FROM oracle_database

------- Data Returned by query --------

device_name read_date   sysdate
Device 1    5/14/2013   22-Sep-14
Device 2    5/14/2013   22-Sep-14
Device 3    5/14/2013   22-Sep-14
Device 4    5/14/2013   22-Sep-14
Device 5    5/14/2013   22-Sep-14
Device 6    5/14/2013   22-Sep-14
Device 7    5/14/2013   22-Sep-14

Results from using TO_DATE

SELECT device_name, TO_DATE(read_date, 'DD-MON-YY'), sysdate

------- Data Returned by query ----------

ORA-01861: literal does not match format string

You have to use a format that matches the strings you actually have stored. You have to hope they are all in the same format and are valid... It's possibly to have a function that tries several formats but that's messay, esp. if you have a mix of DD/MM and MM/DD dates. This is why dates shouldn't be stored as strings... Anyway, if you show some sample data and the dates they represent (if it isn't obvious) then a suitable model can be suggested. — Alex Poole
@Alex, I hope you won't mind if I give a try to answer based on "Why to not store date as string". I have included the link to Ed Stevens' article about it, which is quite awesome. And I use the same link everywhere to answer similar questions. — Lalit Kumar B
@LalitKumarB - while it's valid, I don't think it explains the error the OP is seeing, or what to do about it. (Other than storing as a date; but then the model problem moves to the insert, maybe). But then until the format the strings are in is known, not much else to say... — Alex Poole
@Alex, I thought of telling OP to alter his session with nls_date_format if he is unsure about the nls settings, but I refrained since I amateurs playing more at query level format than session level, and it will override the session level settings. Let's see what OP tells us regarding further details. I will try to improvise my answer based on new inputs by OP. — Lalit Kumar B

Sylvain Leroux Sylvain Leroux · Accepted Answer · 2014-09-22T19:58:34

Given your data, the proper format is very probably MM/DD/YYYY. It will match both single and double digits months or days.

MM : month from 01 to 12 (leading 0 not mandatory by default)
\ : any punctuation sign
DD : day from 01 to 31 (leading 0 not mandatory by default)
\ : any punctuation sign
YYYY : year

See Oracle's documentation about datetime Format Models for the details.

Here is example:

-- Some test data
WITH testdata AS (
  SELECT '5/14/2013' as d FROM DUAL
  UNION SELECT '05/14/2013' FROM DUAL
  UNION SELECT '5/1/2013' FROM DUAL
  UNION SELECT '5/01/2013' FROM DUAL
  UNION SELECT '6-6-2013' FROM DUAL)

-- Actual query demonstrating the use of the MM/DD/YYYY format
select d, TO_DATE(d,'MM/DD/YYYY') FROM testdata

Producing:

D           TO_DATE(D,'MM/DD/YYYY')
05/14/2013  05/14/2013
5/01/2013   05/01/2013
5/1/2013    05/01/2013
5/14/2013   05/14/2013
6-6-2013    06/06/2013

If you really need to enforce the fact that your date components are separated by / (and not by any other punctuation sign), you should use fxfmMM/DD/YYYY instead:

the fx flag force strict comparison, both for punctuation and number of digits in the format
the fm flag relax that comparison to allow up to the number of specified digits

Varchar2 to Date for sysdate comparison

3 Answers