Re: Not a valid month error

From: Scott Mattes <Scott_at_TheMattesFamily.ws>
Date: Wed, 30 Mar 2005 09:39:19 -0500
Message-ID: <7eudna3ii82VJ9ffRVn-pA@adelphia.com>

Karuna wrote:
> I have a table with one of the columns as DDate(char(6) as the data
> type). I am trying to use to_date function in SELECT query to display
> all the records but it's giving "Not a valid month" for all the dates
> after the year 89. I tries to fix it with whatever infomration I could
> get from the Interenet, but I am unable to correct the error.Any ideas
> as to where the problem is?
>

Possibly the data stored in the field has changed format between 89 and 90, which is why the field should have been a DATE format to begin with.

Is it really nesessary to use TO_DATE?

Now that you have the problem you might be able to use DECODE in the select and specify the different date formats that you have in the table. You should look closely at the data to see how many different date formats were used and how to identify them with code.

Not knowing what the data looks like I can only guess, but assuming that your pre-1990 dates look like this mm-dd-yyyy and your post-89 dates look like this yyyy-mm-dd you could try

select DECODE( substr( ddate, 3, 1 ),

                '-', to_date( ddate, 'mm-dd-yyyy',
                to_date( ddate, 'yyyy-mm-dd' )

from my_table; Received on Wed Mar 30 2005 - 08:39:19 CST