Re: Y2K to_date SQL question

Hi,

>select min(to_date(canyymmdd,'YYMMDD')) from mytable
>
>I realize I could modify the database so the field is yyyymmdd, but I'd
>rather not!

You should!

>Is there a SQL command that will return the earliest yymmdd
>for me, taking into account the fact that 000101 is greater than 991230?

You can use the 'old' trick that has been built in, the last decade. Here you suppose that every year after 80 is in the 20th century and the rest in the 21st.

select min(to_date(decode(sign(to_number(substr(canyymmdd,1,2))-80 , -1, '20', '19') || canyymmdd))
from yourtable;

I stongly advise you to change the database structure!

• Martin Jesterhoudt
• martinj(replace this by @)xs4all.nl

  ---