Re: SETTING THE ORACLE DATE

sumanc_at_my-dejanews.com wrote:

> Hi All,
>
> Here is what I'm trying to do - i'm involved in Y2K testing. When users log on
> using their web browsers, they enter a login and password. When they submit
> this, a cgi file checks to see if the password or login have expired, by
> comparing the current date (in SYSDATE) to the creation date(stored in the
> PASSWORD_CREATE_DATE field in a table), and if the difference is more than 90
> days, it takes it that they have expired and prompts the user to create a new
> login and password.
>
> As you all know, come yr 2000, a substraction of SYSDATE -
> PASSWORD_CREATE_DATE will not work cos Oralce stores dates as DD-MON-YY. I
> already know how to change the default format in Oracle by setting the
> NLS_DATE_FORMAT to DD-MON-RRRR. My question to the Oracle gurus is this

Wrong! Oracle does not store a date this way! Oracle has a Y2K white paper on their website that describes this and other date issues for the RDBMS and all their tools.

> :
>
> I have made changes to NLS_DATE_FORMAT so that now when u exec SELECT SYSDATE
> FROM DUAL, I get something like '08-APR-1999', so I know I can safely carry
> out substractions like SYSDATE - PASSWORD_CREATE_DATE. What I want is to test
> my script with the year set to 2000 in Oracle. Which means I need SYSDATE to
> be set to 2000, so that when my SQL query is ready to substract
> SYSDATE-PASSWORD_CREATE_DATE, the date used is something like '10-JAN-2000',
> instead of the current date. Basically, I need to know how to alter the
> Oracle clock so that SELECT SYSDATE FROM DUAL returns a date in 2000 (to
> whatever i set the Oracle clock to), instead of today's date.

Oracle does not have a clock. It makes calls to the server (UNIX etc.) to get the system date. You will need to roll your server forward to get this.

>
>
> Any ideas will be greatly appreciated.
>
> Thanks,
>
> Suman
>
> p.s : Please e-mail me your responses as I might not be able to find your
> replies in the labyrinth of posts that are already up here.
>
> Thank you once again.....Suman (sumanc_at_lucent.com)
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Received on Thu Apr 08 1999 - 11:32:25 CDT