Re: [Q] ORACLE date select problem?

Using the century pivot date format doesn't automatically guarantee y2k compliance... it's really sort of a half baked fix that assumes any relevant dates are going to be no more than 50 years away from the turn of the century, so your application would drive the decision whether or not to use this (a database of nursing home resident's demographic data comes to mind as an example of where century pivot date formatting would probably NOT be a good idea :). The only way to be absolutely sure would be to use the to_date function, like so:

   select account_id, datestamp from table1    where datestamp < to_date('31-DEC-1996','DD-MON-YYYY);

David Peasley
Database Administrator
Adams State College
Alamosa, CO 81102
david_at_adams.edu

"Hsu, Anthony C., ,CPMS" wrote:

> Before you run the SELECT query
> try to run:
> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RRRR'
> then you will have Y2K compliance session.
>
> -----Original Message-----
> Sent: Friday, October 13, 2000 11:35 AM
> To: Multiple recipients of list ORACLE-L
>
> On ORACLE 7.3.4 database, I tried to select date less than "31-DEC-96".
> the following statement does NOT work correctly:
>
> select account_id, datestamp from table1
> where to_char(datestamp,'DD-MON-YY') < '31-DEC-96';
>
> It still print date like '21_JAN-98'.
>
> Anyone can give me a hint?
>
> Thanks
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: L
> INET: leed_at_chele.cais.net
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Hsu, Anthony C., ,CPMS
> INET: anthony.hsu_at_cpms.osd.mil
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Fri Oct 13 2000 - 12:39:25 CDT