CODEWITHSUNDEEP
sqloracle-databaseto-date
user1751356
user1751356

Reputation: 615

Oracle to_date error

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.

Upvotes: 0

Views: 1023

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

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

Upvotes: 1

user5683823
user5683823

Reputation:

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

Upvotes: 0

Related Questions