Re: varchar to date

From: Matt B. <gtimatt_at_home.com>
Date: Thu, 29 Nov 2001 03:02:22 GMT
Message-ID: <2zhN7.32171$Sx.9193170_at_news1.elcjn1.sdca.home.com>


[Quoted] "longinus" <longinus_at_zeus.polsl.gliwice.pl> wrote in message news:3C040C2A.36EC3B0B_at_zeus.polsl.gliwice.pl...
> Date is stored in 'VARCHAR' field.
> SQL> select min(end_date)
>
> brings:
>
> MIN(END_DA
> ----------
> 15.10.2001
>
> I want to convert end_date (VARCHAR) for date and choose the oldest
> one..
> SQL> select min(to_date(end_date,'DD.MM.YYYY'))
> reached';
> ERROR:
> ORA-01839: date not valid for month specified
>
> What is wrong?????

The to_date function is being done before the MIN function. TO_DATE is operating on all the rows in your query first and then MIN is being applied. There's a row in your table that has a value that is giving you the error and it's not the 15.10.2001 you are expecting.

If you do this:

to_date(min(end_date),'DD.MM.YYYY'))

...it will work w/o error because it'll find the mininum END_DATE first, which you know to be 15.10.2001 and since this is in the proper format it will not error.

However, while it'll work w/o error, it probably isn't what you want. It'll find MIN in a VARCHAR context first and then apply TO_DATE after that and then it's not really the MIN value in a DATE context.

Anyway, select all your rows in the query w/o MIN and TO_DATE for now, fix the values that are wrong, and try it again and it'll work.

-Matt Received on Thu Nov 29 2001 - 04:02:22 CET

Original text of this message