Home » SQL & PL/SQL » SQL & PL/SQL » How to change systimestamp 04-Jul-07 11.11.11.779000 am +05:30 to 04-Jul-07 11.11.11.779000 +05:30
How to change systimestamp 04-Jul-07 11.11.11.779000 am +05:30 to 04-Jul-07 11.11.11.779000 +05:30 [message #249312] Wed, 04 July 2007 01:19 Go to next message
pvr_msg
Messages: 7
Registered: August 2006
Location: hyderabad
Junior Member
Hi to every body,

Plz tell how to change database server timestamp.

In my server the present systimestamp is

SQL>select systimestamp from dual;

04-Jul-07 11.11.11.779000 AM +05:30

SQL>SELECT DBTIMEZONE FROM DUAL;
+05:30

SQL>SELECT SYSDATE FROM DUAL;
04-JUL-07



ACTUAL PROBLEM IS IN MY SCHEMA THERE IS ONE TABLE.
IN THAT ONE COLUMN IS TIMESTAMP(6) WITH TIME ZONE.

WHEN I AM INSERT INTO ONE ROW FOR THAT TABLE IT SHOWS ERROR LIKE

ORA-01849 HOUR MUST BE BETWEEN 1 AND 12.(THAT PARTICULAR COLUMN)
(MY INSERTED ROW LIKE 1,'RAMANA','04-Jul-07 00.00.00.000000 +05:30)
WHEN I AM SET LIKE

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-YYYY HH24:MI:SS.FF TZH:TZM';
session altered

SQL> THEN I AM INSERT THE ABOVE ROW IT IS INSERTED.

BUT IN ANOTHER SERVER IN MY OFFICE

SQL> SELECT SYSTIMESTAMP FROM DUAL;

04-Jul_07 11.11.11.779000 +05:30

SQL> select dbtimezone from dual;
-07:00
sql> when i am insert the above row it is inserted
(MY INSERTED ROW LIKE 1,'RAMANA','04-Jul-07 00.00.00.000000 +05:30)

so please tell how to change systimestamp to

04-Jul-07 11.11.11.779000 +05:30 rather than 04-Jul-07 11.11.11.779000 AM +05:30

PLZ SEND ANSWER AS SOON AS POSSIBLE

THANKS IN ADVANCE.
BY
RAMANA.



Re: How to change systimestamp 04-Jul-07 11.11.11.779000 am +05:30 to 04-Jul-07 11.11.11.779000 +05: [message #249319 is a reply to message #249312] Wed, 04 July 2007 01:39 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
FIRST,
KEEP OFF YOUR FINGER FROM SHIFT key.

Then,
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Now,
Just change your query to convert your string to a timestamp using the TO_TIMESTAMP function and NEVER rely on implicit conversion.
Then you will no more get errors.

Regards
Michel
Previous Topic: how to get the column headings in out bound interface
Next Topic: compare two similar tables in different Schemas n the schemas in different databases
Goto Forum:
  


Current Time: Sat Dec 10 20:52:04 CST 2016

Total time taken to generate the page: 0.27202 seconds