Re: Y2K failed, Oracle 7.3.4

On Fri, 14 May 99 06:41:46 GMT, hans.xie_at_its.CSIRO.AU (Hans Xie) wrote:

You are seeing the discrepancy because in Statement 1, you are not comparing dates but strings. In Statement 2 you are comparing dates and hence the correct result.

TO_CHAR is converting the date to a char and thus the comparison operator '>' is comparing two strings.

-sm

>Hi all,
>
>PROD system is: Solris 2.5, Oracle7 Server Release 7.3.2.3.0, SQL*Plus:
>Release 3.3.2.0.0, and UNIX date is today(14,May 1999)
>
>Y2K test machine is: Solaris 2.6, Oracle7 Server Release 7.3.4.1.0, SQL*Plus:
>Release 3.3.4.0.1, and UNIX date is 2,Jan,2000
>
>1. & and &&
>Two my programs use single & in SQL failed in Y2K machine. No error message,
>but the SQL returns nothing when it should return tens of rows. While my
>other programs with double & in SQL are fine in Y2K machine. The paratemer
>only occurs once in all my programs, so single & should be the same as double
>&&. In our PROD, single & or double && both are fine. Exactly the same
>programs in both PROD and Y2K machine.
>
>2. to_char(add_months(sysdate,-1))
>Statement 1:
>SELECT count(*)
> FROM table1
> WHERE create_date > to_char(add_months(sysdate,-1))
>returns 32 in our PROD
>
>Statement 2:
>SELECT count(*)
> FROM table
> WHERE create_date > sysdate-31
>returns 32 as well in our PROD
>
>create_date is defined as DATE in table.
>
>But, in our Y2K machine, statement 1 returns 0, statement 2 returns 32.
>To get it even worse SELECT to_char(add_months(sysdate,-1)) FROM dual in Y2K
>machine returns correct date, ie '2 Dec 1999'
>
>Any comments are most welcomed.
>
>Hans Xie
Received on Tue May 18 1999 - 20:43:59 CDT