Re: Y2K to_date SQL question

jim_at_jpcr.com (Jim Pistrang) wrote:

>Y2K to_date SQL question
>
>Hi all,
>
>I've got some data that's fed in from an old system. There's a cancel
>date field named canyymmdd thats a 6 character field in yymmdd format. I
>convert it to a date field for most functions, but there's one place where
>I need to return the earliest date in a table using the yymmdd format.
>Before Y2K I could do this:
>
>select min(canyymmdd) from mytable;
>
>I was hoping I could get around Y2K by doing this, but it doesn't work:
>
>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! Is there a SQL command that will return the earliest yymmdd
>for me, taking into account the fact that 000101 is greater than 991230?
>
>tia
>
>Jim

Try select min(to_date(canyymmdd,'RRRRMMDD')) from mytable....

the RRRR format will treat years 00-49 as 2000 to 2049 and years 50-99 as 1950-1999 so it should handle the min function correctly...

I would advise, however, that unless you have many,many records, converting to yyyy will insure that future DBAs of your system will bless you...... To reply please remove the 'nospam' part of the address Received on Thu Oct 15 1998 - 12:38:17 CDT