Re: Not a valid month error

"Karuna" <mrinalini.sukumar_at_gmail.com> wrote in message news:1112220761.905417.48690_at_z14g2000cwz.googlegroups.com...
>
> Dave wrote:
> > "Karuna" <mrinalini.sukumar_at_gmail.com> wrote in message
> > news:1112209818.353192.8700_at_f14g2000cwb.googlegroups.com...
> > >
> this error in this situation?
> > >>
> > select to_date(orderDate,'mmddyy') from order; !!!!!!!!
> >
> > gee whiz
>
> Well I had tried that as well and it's not working
>

Real easy. Change the column from a character to a date. You shouldn't store "dates" in a column that holds strings. Whomever came up with that design doesn't know the basics. Would you store characters in a numeric column as ASCII numbers? Of course not.

Write a pl/sql function that converts a string to a date except when it gets an error, then return the string and the word 'ERROR!!!' then do

select orderDate from order where myFunction(orderDate)='ERROR!!';

eg (not compiled or tested)

create or replace myFunction(v_date in varchar2) return varchar2 is begin

    return to_char(to_date(v_date,'mmddyy'); exception when others then

    return 'ERROR!!';
end;
/

But change the damn table and fire the fool who designed it! Jim Received on Wed Mar 30 2005 - 21:06:41 CST