Re: Y2K to_date SQL question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 15 Oct 1998 16:34:05 GMT
Message-ID: <362922d2.13398766_at_192.86.155.100>

A copy of this was sent to jim_at_jpcr.com (Jim Pistrang) (if that email address didn't require changing) On Thu, 15 Oct 1998 11:40:15 -0400, you 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

Have you looked at the RR format in the date mask? (see the sql reference manual for a complete discussion of RR and RRRR formats and their implications).

Here is an example using the RR format:

SQL> alter session set nls_date_format = 'DD-MON-YYYY';

Session altered.

SQL>
SQL> create table t1 ( x varchar2(10) );

Table created.

SQL> 
SQL> 
SQL> insert into t1 values ( '000101' );

1 row created.

SQL> insert into t1 values ( '990101' );

1 row created.

SQL> 
SQL> 
SQL> select to_date( x, 'rrmmdd' ), x

  2 from t1
  3 order by to_date( x, 'rrmmdd' )
  4 /

TO_DATE(X,' X

----------- ----------
01-JAN-1999 990101
01-JAN-2000 000101

SQL>
SQL> select min( to_date( x, 'rrmmdd' ) )   2 from t1
  3 /

MIN(TO_DATE

---

01-JAN-1999 SQL> select max( to_date( x, 'rrmmdd' ) )   2 from t1
  3 /

MAX(TO_DATE

---

01-JAN-2000   Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

Received on Thu Oct 15 1998 - 18:34:05 CEST