Re: dates and 2001
Date: Thu, 22 Dec 1994 02:58:56 GMT
Message-ID: <D16yyB.5sn_at_gremlin.nrtc.northrop.com>
In article <D0xsBt.F5w_at_gremlin.nrtc.northrop.com> mresnick_at_world.nad.northrop.com (Mike D. Resnick) writes:
>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;
OOPS! This *should* have read as follows:
update table set field=field+36525
where field<=to_date('2433283','j') and field is not null;
^^^^^^^^^ ^^^^^^
Sorry about that. Thanks to the person that questioned how this worked. :-)
MR.
>
>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 Thu Dec 22 1994 - 03:58:56 CET