Change system date with Oracle sql statement

[Reshmi Jacob]

Hi all,

Can any one help me with a command by which I can change the system date thru Oracle



Regards
Reshmi

[pragatiswain]

Oracle takes System date from the OS. Shut down Oracle, Change the OS date and restart Oracle. That will give you New Systemdate.

If you do not Shut down/ Restart Oracle, it some time creates problems.

Hope this helps.

[Reshmi Jacob]

Hi

Thanks for ur time.

But its not the thing I want. The exact problem is when I make changes in a table, and if some condition satisfies, I will execute a trigger in which I will change the system date itself. (ie; UPDATE.... SYSDATE = '27-Nov-2006') This will create an error , then how can I change the SYSDATE ???

Regards
Reshmi

[kasibhat]

Use 1)nls_format_da te

To Change Date Format Or Use

2)to_char, 3)or To_date

[pragatiswain]

Use 1)nls_format_da te

To Change Date Format Or Use

2)to_char, 3)or To_date

Hey,

Reshmi is trying to change the SystemDate, not the date format.
It can be done, using a batchfile/shell script, if you want it to be automatic.

[Reshmi Jacob]

Hey,

Reshmi is trying to change the SystemDate, not the date format.
It can be done, using a batchfile/shell script, if you want it to be automatic.

Hi

Can u help me in how to execute a batch file from Oracle?

Reshmi

[pragatiswain]

Hi

Can u help me in how to execute a batch file from Oracle?

Reshmi

sqlplus "/as sysdba"; SQL> shutdown immediate
Stop listener as - lsnrctl stop[listener_name]

date [New Date]

Start listener as - lsnrctl start[listener_name]
Start Database as - sqlplus "/as sysdba"; SQL> startup

hope this helps.
By the way, the above script is for Windows. what are you trying to do?
If you ask your Oracle Admin. they will do it for you.

[suvam]

OK , you may proceed as Pragati has told u .
But through a DML statement like UPDATE it is not possible as SYSDATE works like a function and it can't be used in the left hand side of a = operator .

[kishorebagul]

Hi

Can u help me in how to execute a batch file from Oracle?

Reshmi

u can execute batch file with
@path/filename from sql prompt

[seraphstream]

...uhm it seems very stange.

Now I try to think about it...

bye

All oracle Errors Messages

Oracle Forum

[vassagus]

hi Reshmi Jacob, maybe you can use this durty trick,

Oracle takes the date from SO, but you can also set a constant date temporality, and then set the S.O date again.

ALTER SYSTEM SET FIXED_DATE = '2007-01-01';

Done!!, you now have a constant sysdate, lol, but prolly you want to go back to the normal clock, couse this is constant and tomorroy will have the same date.

ALTER SYSTEM SET FIXED_DATE='non e';

Cool, now you have your incremental time again.

I hope this can help you, remember that the user must have alter system grant, and this can be dangerous depending of your porpose.

Good lock, and sorry for my bad english(latin guy).

Bye.