Re: SETTING THE ORACLE DATE

The date must be changed on the box i.e.operating system date. Or you can add nine months to sysdate to get a a 2000 date. Just to let you know Oracle stores the full date and time in the database regardless of what NLS format you are using. What you are talking about with the DD-MON-YY is just a mask. If you do a to_date(field, 'FORMAT') you can view all the dates in you tables any way you like. Try with the format 'MON-DD-YYYY:MM:SS' this will show you the full date and time the column was stored. Just remember all the information on the date is stored.

Bill

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 :
>
> 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.
>
> 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

--
William Dangren
Navis Enterprises
407.348.6401
800.219.0207 Received on Thu Apr 08 1999 - 11:20:30 CDT