Re: dates and 2001

From: Mike D. Resnick <mresnick_at_world.nad.northrop.com>
Date: Sat, 17 Dec 1994 03:57:25 GMT
Message-ID: <D0xsBt.F5w_at_gremlin.nrtc.northrop.com>

In article <3ca3l2$54n$1_at_mhadg.production.compuserve.com> Frank Greene <74200.427_at_CompuServe.COM> writes:
>How do you write a BETWEEN retrieval statement that works for
>dates which span the century mark? I have tried
>
>SELECT the_date_field
>FROM the_table
>WHERE TO_CHAR(the_date_field, 'mm/dd/yyyy')
>BETWEEN '06/12/1994' AND '06/12/2003'
>;
>
>But it only returns dates up through the end of the current
>century.

Possibly a couple of problems....
First off, you should probably change the above to look something like this:

   SELECT  the_date_field 
     FROM  the_table
    WHERE  the_date_field

   BETWEEN to_date('06/12/1994') and to_date('06/12/2003');

This will speed up the SQL call because the to_char conversions do not need to be made on every row and the TO_CHAR function in the where clause will cause Oracle to ignore any index on that field.

The real problem is probably due to the actual value of the date stored in the table. If you store dates with only the last two-characters of the year, Oracle will assume that they are the current century.

This can be controlled by the value of the NLS_DATE_FORMAT parameter or the format model in the sql code. I've changed our NLS_DATE_FORMAT to be 'DD/MM/RR' and things are working fine. When we're dealing with dates in the 1950-2049 range. If outside that range, you should always use four-character years.

Refer to page A-33 of the Oracle7 Server Administrator's Guide. (You may also have to "fix" your data because dates of 2003 were stored as 1903 ;) ) If you're dealing with dates of 18xx or 21xx, I would suggest that you always give Oracle the four character year.

I've used the following sql to correct the dates (assuming I only have dates from 1950-2049) in some of our tables:

update table set field=field+36525 where field<=2433283 and field is not null;

Hope this helps.....

                                 _                          _  
     |\ | _ ._ _|_ |_ ._ _ ._   /__ ._   ._ _ ._ _  _.._   /  _ ._ _
     | \|(_)|   |_ | || (_)|_)  \_| | |_|| | || | |(_|| |  \_(_)| |_). 
/--------------------------|---.----------------------------------|----------\
| Michael D. Resnick           |     mresnick_at_world.nad.northrop.com         |
| Northrop Grumman Corporation |     Voice: 310.332.7807                     |
| Military Aircraft - ILS LIS  |     Fax:   310.332.7816                     |
| One Northrop Ave.  2555/W6   |---------------------------------------------|
| Hawthorne, CA  90250         | "Someday I'll quote myself and put it here" |
\------------------------------^---------------------------------------------/

Received on Sat Dec 17 1994 - 04:57:25 CET