| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Date Bug?
On Wed, 28 Jan 1998 15:50:12 -0500, Tim Bouffard <bouffard_at_umis.upenn.edu> wrote:
>Could someone take a crack at explaining to me why this doesn't return
>the same results? It almost seems like a bug...
>
>SQL> get q1
> 1 select
> 2 sign(s_bpe-to_date('JAN-96','MON-YY')), s_bpe,
> 3 sign(TO_DATE(S_BPE)-to_date('JAN-96','MON-YY')) WRITE,
> 4 TO_DATE(S_BPE)
> 5 from
> 6 parm_70464
> 7 where
> 8* seg4 = '5VB010'
>SQL> /
>
>SIGN(S_BPE-TO_DATE('JAN-96','MON-YY')) S_BPE WRITE TO_DATE(S
>-------------------------------------- --------- ---------- ---------
> -1 01-NOV-97 1 01-NOV-97
>SQL> desc parm_70464
> Name Null? Type
> ------------------------------- -------- ----
>...[SNIP]...
> S_BPE DATE
>...[SNIP]...
I don't think it's a bug, I'm almost certain your S_BPE date is from
19th or earlyer century, which causes "inconsistent" result of your
query. Try seting 4digit year in your default date format and both of
your SIGN functions will return the same result (i.e. -1).
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; The reason behind this behavior is that you are using TO_DATE function on DATE column inside your second SIGN function. To perform this, Oracle must firs implicitly perform TO_CHAR(S_BPE) with default format mask (and thus loosing century from your date!). When this resulting string is then converted back to date, the default century (20th) is assumed for the year part of the date. That way, the converted date becomes greater then 'JAN-97', which in fact is not true as your second SIGN function correctly shows.
To avoid this either set your default date format to use four digit years or add TO_CHAR function after your TO_DATE in the second SIGN expression and use four-digit-year format mask inside your TO_DATE and TO_CHAR functions in the second SIGN expression:
... sign(TO_DATE(TO_CHAR(S_BPE,'DD-MON-YYYY'),'DD-MON-YYYY') -
to_date('JAN-96','MON-YY')) WRITE, ......
Regards,
Jurij Modic Republic of Slovenia jurij.modic_at_mf.sigov.mail.si Ministry of Finance ============================================================The above opinions are mine and do not represent any official standpoints of my employer Received on Thu Jan 29 1998 - 00:00:00 CST
 |
 |