Date format discrepancies [message #435932] |
Sat, 19 December 2009 03:27  |
ss1_3
Messages: 4 Registered: December 2009
|
Junior Member |
|
|
There's a table dt_tbl in our schema which has date related information till Nov-2011.I used the following script to populate it further:
insert into dt_tbl
(dt_id,
...
)
select
3010 + trunc((curr_dt + rnum)- to_date('11-Nov-11')) dt_id,
..
from
(
SELECT
TO_DATE( '11/11/2011', 'MM/DD/YYYY' ) curr_dt,
rownum rnum
FROM dual
CONNECT BY level <=416
)
The above script worked correctly in dev environment and new rows were inserted with dt_id like 3011..3012 and so on.But when the same script was run in stage/prod env, it resulted in rows with dt_id values like 768662..768663..and so on.We found out that 11-nov-11 was getting interpreted as 11-nov-0011 (not 2011).So we changed the select query as
select
3010 + trunc((curr_dt + rnum)- to_date('11-Nov-2011')) dt_id
and this time it worked correctly.
I want to know why this happened and how I could have avoided this blunder?
Note : I checked the NLS_DATE FORMAT which was DD-MON-RR in all three env.
[Updated on: Sat, 19 December 2009 03:32] by Moderator Report message to a moderator
|
|
|
|
Re: Date format discrepancies [message #435938 is a reply to message #435932] |
Sat, 19 December 2009 03:48   |
ss1_3
Messages: 4 Registered: December 2009
|
Junior Member |
|
|
Hi Michel,
Thanks for the prompt reply.
But I wonder why the query is showing error.Its working perferctly fine on my system(on both oracle 9i and 10g).
Anyway,my question still remains unanswered.I agree date format parameter can be specified but still why different results on dev and stage/prod env,though both have same nls_date format.
Thanks.
|
|
|
Re: Date format discrepancies [message #435939 is a reply to message #435938] |
Sat, 19 December 2009 03:53  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote:But I wonder why the query is showing error.
For the reason I mentioned: you can't know (unless you master write yourself all the parameters from client to server) what is the current default format.
Quote:why different results on dev and stage/prod env,though both have same nls_date format.
You think it is but it is not.
Regards
Michel
|
|
|