Home » SQL & PL/SQL » SQL & PL/SQL » Date format discrepancies
Date format discrepancies [message #435932] Sat, 19 December 2009 03:27 Go to next message
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 #435934 is a reply to message #435932] Sat, 19 December 2009 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It could be worst (or maybe not worst):
SQL> select to_date('11-Nov-2011') from dual;
select to_date('11-Nov-2011') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

TO_DATE takes 2 parameters use them BOTH, otherwise you use default format which you can't know what it is.

Regards
Michel

Re: Date format discrepancies [message #435938 is a reply to message #435932] Sat, 19 December 2009 03:48 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: number to char
Next Topic: display two digits after decimal point,
Goto Forum:
  


Current Time: Sun Sep 25 16:16:51 CDT 2016

Total time taken to generate the page: 0.11189 seconds