| how to change default timestamp format at DB level? [message #63542] |
Thu, 14 October 2004 20:32  |
Shilpa
Messages: 59
Registered: November 2001
|
Member |
|
|
Hi,
I want to change the default timestamp/date format ('DD-MON-YY HH12:MI:SSXFF AM') at oracle9i DB level to 'MM/DD/YYYY HH24:MI:SXFF'
I tried with setting the NLS_TIMESTAMP_FORMAT in init.ora parameter.
It shows the proper value for NLS_TIMESTAMP_FORMAT(i.e. 'MM/DD/YYYY HH24:MI:SXFF') but takes the format 'DD-MON-YYYY HH12:MI:SSXFF'
as the default. Also tried with "ALTER SYSTEM....." but didnt succeed. Actually this parametr belongs to territory....but changing it also doest work.
We have created a new territory with default format.... it takes that territory....but it works at session level.
If anyone can add other info.
Thx in adv.!
|
|
|
|
|
|
| Re: how to change default timestamp format at DB level? [message #63650 is a reply to message #63593] |
Mon, 25 October 2004 00:53  |
Shilpa
Messages: 59
Registered: November 2001
|
Member |
|
|
Hi,
whatever u suggested is right. I tried with this also.
It shows the reqd value using "SHOW PARAMETER" but still format remains same.
We are in the DW hsg. Gp. . For one of our prj., we are using Informatica(ETL tool) fro the development. Our source & target db is oracle9i itself.
As informatica6 doesn’t support “timestamp” data type, we have used “STRING” as the datatype in Informatica. Now while loading to the target “STRING” should be implicitly converted to “TIMESTAMP” . It works fine if the string format is “DD-MON-YYYY HH:MI:SSXFF AM” as this is the default format for our DB. But our client is using “MM/DD/YYYY HH24:MI:SS.XFF”as the default format which is set at DB level.
So we cudnt test here on the format “MM/DD/YYYY HH24:MI:SS.XFF”. So we want to change this format to “MM/DD/YYYY HH24:MI:SS.XFF” at DB level instead of “DD-MON-YYYY HH:MI:SSXFF AM”.
We can set the format in the session property (pre sql cmd ) of informatica but this opens the other session & process the mapping in different session. So cudnt satisfy our requirement.
We tried by creating the new TERRITORY & by setting the default timestamp format in it but it accepts the terriotory and alos shows the regd. Format in NLS_PARAMETER , but the default timestamp remain as it is.
Cud u guide me in the same??
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
