Re: Easy date problem

<balanagu_at_my-deja.com> schreef in berichtnieuws 8gmfm4$i3c$1_at_nnrp1.deja.com...
> Hello everybody,
> I cannot query by date. I am having some kind of problem with dates.
> Here are the outputs.
>
> I am **** ing the soc_sec_nbr for privacy. But I am using the same
> soc_sec_nbr on both statement.
>
> select edate from narrative where soc_sec_nbr = '************';
> EDATE
> --------------------
> 05-OCT-95
> 18-OCT-95
> 01-DEC-95
> 04-DEC-95
> 04-DEC-95
> 07-DEC-95
> 07-DEC-95
> 07-DEC-95
> 07-DEC-95
> 08-DEC-95
> 12-DEC-95
> 13-DEC-95
> 15-DEC-95
> 15-DEC-95
> 22-DEC-95
> 22-DEC-95
> 27-DEC-95
> 27-DEC-95
> 28-DEC-95
> 29-DEC-95
> 02-JAN-96
> 05-JAN-96
> 11-JAN-96
> 11-JAN-96
> 12-JAN-96
> 18-JAN-96
> 18-JAN-96
> 23-JAN-96
> 24-JAN-96
> 24-JAN-96
> 25-JAN-96
> 26-JAN-96
> 29-JAN-96
> 29-JAN-96
> 01-FEB-96
> 20-FEB-96
> 29-FEB-96
> 01-MAR-96
> 19-MAR-96
> 19-MAR-96
> 28-MAR-96
> 19-APR-96
> 01-MAY-96
> 08-MAY-96
> 14-JUN-96
> 26-JUN-96
> 25-JUL-96
> 30-JUL-96
> 06-AUG-96
> 13-AUG-96
> 15-AUG-96
> 30-AUG-96
> 10-SEP-96
> 16-SEP-96
> 22-DEC-96
> 55 rows selected.
>
>
> SQLWKS> select edate from narrative where soc_sec_nbr = '***********'
> and edate > to_date('01-JAN-96');
> EDATE
> --------------------
> 0 rows selected.
>
> I wonder why? What am I doing wrong?
> Thanks in advance.
> Bala
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

Indeed easy
Your default date mask, NLS_DATE_FORMAT equals DD-MON-YY. Any date part you don't specify in your date, is 'borrowed' from the sysdate.
So you'r actually querying : > '01-JAN-*20*96' You can resolve this in various ways, however IMO the best solution is not to rely on a default date mask and explicitly specify one always (In the past I had to resolve many nasty problems because developers 'assumed' a default date mask and didn't specify one) So either
to_date('01-JAN-96','DD-MON-RR') /* where RR will consider anything >= 50 as 20th century */
or
to_date('01-JAN-1996','DD-MON-YYYY')

Hth,

Sybrand Bakker, Oracle DBA Received on Fri May 26 2000 - 00:00:00 CDT