Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-01839 date not valid for month specified error message

john dickey mccarthyJun 4 2010 — edited Jun 8 2010
Okay, the challenge is to try and describe my issue clearly.

I am wanting to write an SQL statement and in that statement I am needing to compare the value of a character field to a date field. The WHERE clause that I am specifically doing includes the following -

TO_DATE(bv1.version_name,'MM/DD/YYYY') <= wip.pa_date

VERSION_NAME is my character column/field. It is intended to hold a date type value, in the specified format (for example, a value = '05/31/2010'). Of course this being the real world and an alpha field, the contents of this column often don't match the desired format. So I added some more stuff to the WHERE clause -

bv1.version_name LIKE '__/__/____' AND bv1.version_name BETWEEN '01/%' AND '12/%'

That took care of most of my problems. However I still run into an occasional ORA-01839 error. I do understand why. I still have a few goofy values in the table. For example, I may have a row where version_name = '02/29/2009'. That value meets my desired format, and the month is between 01 and 12, but that clearly is not a valid date. Of course some Februaries day 29 is a valid day. So I don't want try and do something like BETWEEN '02/01%' AND '02/28%', as then I may eliminate a good record.

So here is my question - is there any way to work around this kind of situation? Either bypass the row in error, or somehow convert that bad date value to say the last day of the month? I have been reading through the Oracle SQL manual, but not finding anything that talks about this kind of situation (just may not have found the right place yet). I would think that when you have a function to convert a character value to a date value, that there would be something to handle this kind of situation, but maybe there is not. I could hard wire an edit for specific dates, but since I want this to be a SCRIPT to build a summary table, I don't want to have to keep checking to see if I need to modify the script. So looking for something that would automatically handle this situation.

I hope this makes sense. If any questions or need more information, let me know. Thanks for your time and assistance.

John Dickey

Comments

Frank Kulash
Hi, John,

The following thread contains a simple user-defined function that can help you:
3880849
to_dt (l.version_name, 'MM/DD/YYYY')
will return NULL if the 1st argument is either NULL or an invalid date (in the format of the 2nd argument).
If the 1st argument is a valid date, then to_dt will return that DATE value.

Edited by: Frank Kulash on Jun 4, 2010 5:33 PM

The following thread includes a sesquipedalian CASE expression for validating dates in pure SQL, without a user-defined function.
4255051
unknown-698157
It is amazing that after this complete novel one realizes your contribution still doesn't contain a four-digit version number (and short-circuited evaluation is not available in all releases).

The long answer is the only solution is to keep the dirt out.
When you load data from an external source, do NOT load it directly into production tables in the correct format.
So I set up a staging table, and add an extra status column and a message column, which both start out as NULL.
Then I UPDATE that column with an 'E' as soon as my where clause detects an error.
When it comes to transfer to production my data is clean as only records with NULL status columns are accepted.
In this particular case you consider could writing a small PL/SQL function which simply reads

function massage(p_junk in varchar2) return date is
d date;
date_error exception
pragma exception_init(-1839, date_error);
begin
d:= to_date(p_junk);
return d;
exception
when date_error then
return NULL ; -- or whatever you want;
end;
/

You could use this in an extra update statement to clean up the column before you are up to final conversion. You could tag the data with an 'E' in a status column.

Hth

------------
Sybrand Bakker
Senior Oracle DBA
Uhh, the issue is really more with Oracle application design deficiency. I have submitted an enhancement request via Oracle Support, but of course no guarantee that the enhancement request will be accepted. We are staying away from customizing Oracle (too much trouble when it comes to upgrades to newer versions). In Oracle Projects, in budgeting, on the budget versions, Oracle does not have a version date column. That is the design deficiency. So what we are doing, for better or for worse, is to use the version_name column to input a version date value. The version name column is a VARCHAR2 defined column. So the Oracle form is going to let people enter information into that column/field that is not in the desired format. So we are having to work around the design deficiency as best we can. I know I will have bad data (invalid date values) to deal with as a result. Certainly not what I would prefer, but trying to work with the lesser of all evils here. Most of the time the users do correctly enter the desired value, in the desired format. So my problem is pretty rare - I have calculated a 0.1% error rate. So just trying to find the best way to deal with these rare errors, and I think we have found a way -

SUBSTR(bv1.version_name,7,4)||SUBSTR(bv1.version_name,1,2)||SUBSTR(bv1.version_name,4,2) <= TO_CHAR(wip.pa_date,'YYYYMMDD').

Still not a perfect solution, but it eliminates the ORA-01839 error message, and it is pretty flexible. I don't have to checking every month to see if the day value exceeds the correct number of days (because potentially I could have a value like "04/32/2010" to deal with). This is not just a February issue.

John Dickey
I have since come up with a better way -

LAST_DAY(TO_DATE(SUBSTR(bv1.version_name,7,4)||SUBSTR(bv1.version_name1,2),'YYYYMM')) <= wip.pa_date


Say I have a version name value = "03/33/2010". What the above logic does is create a character value (from the substrings) of "201003". The TO_DATE converts that to 03/01/2010. The LAST_DAY then converts that to 03/31/2010. So I eliminate my ORA-01839 error, which was my main goal, and get a decent date as well.

John Dickey
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 6 2010
Added on Jun 4 2010
4 comments
18,122 views