Oracle to_date error

Question

I have a source application which is using varchar type to store dates. All the dates are supposed to be in yyyymmdd form.

So now, when I run this query

select (case when PLE_DATE = '00000000' then null else  to_date(PLE_DATE,'yyyymmdd') end) PLE_DATE
from PLE_DATE_TAB

i'm able to see the data from sqldeveloper perfectly fine. But when i'm trying to insert this data into another table with target as date column. It is throwing error date of month must be between 1st and last day of the month. I'm struggling to figure this out . Any help is much appreciated. Both source and target tables are in oracle db.

**varchar type to store dates** why ? and why not `Timestamp` or `date` datatype ? — Pரதீப், Dec 10 '16 at 01:43
that's a good question. I think its some legacy application that they are using to store date in varchar form. In this case, i dont have a choice but to deal with what i have got. — user1751356, Dec 10 '16 at 01:45
i dint understand the question. How can i show data present in ple_date? — user1751356, Dec 10 '16 at 01:45
`select PLE_DATE from PLE_DATE_TAB` run this and copy paste the result in question. My guess is, all the records are not in this format `yyyymmdd` — Pரதீப், Dec 10 '16 at 01:47
its a lot of data. Even when i do distinct im getting 9000 distinct results. — user1751356, Dec 10 '16 at 01:49
is there a way to write a query of some sort to filter the junk or bad data out? — user1751356, Dec 10 '16 at 01:51
Am not a Oracle guy.. Not very good in Oracle date functions — Pரதீப், Dec 10 '16 at 02:28

Wernfried Domscheit · Answer 1 · 2016-12-10T08:45:17.450

1

Write a function

Create function Junk_to_date(junk in VARCHAR2) return date as

Begin 

Return to_date(junk,'yyyymmdd');
Exception 
   When others then
       Return null ;
End;

Then use it in your select.

Select Junk_to_date(PLE_DATE)
From...

edited Dec 10 '16 at 08:45

answered Dec 10 '16 at 08:05

Wernfried Domscheit

54,457
9
76
110

score 0 · Answer 2 · answered Dec 10 '16 at 02:09

Let's assume for now that the data is indeed meant to be in 'yyyymmdd' format. (You could write a simple query to select all rows where this date string is not exactly eight characters, or contains non-digits, but those possibilities would lead to different error messages, not the one you got.)

To check for "bad dates" you could do something like this:

select ple_date from ple_date_tab 
where to_number(substr(ple_date, 7, 2)) = 31 
  and to_number(substr(ple_date, 5, 2)) in (2, 4, 6, 9, 11)
;

You may write something similar to find all the dates of February 29 and checking the year to make sure it's a leap year. Or perhaps to look for day equal to 00 instead of 01 or higher, or day greater than 31, in any month (the second part of the WHERE condition wouldn't be needed then).

If this doesn't help, we can think about other things, but this is what I would check first (based on the error message you received).

Oracle to_date error

2 Answers2