We help IT Professionals succeed at work.

Date got changed by oracle

avi_ny
avi_ny asked
on
Medium Priority
451 Views
Last Modified: 2010-03-22
Hi,
I have this strange problem.
My data file have data like
"S11",1/31/1948,3.4
"S12",2/29/1948,3.8
"S1",10/31/1949,7.9
"S2",11/30/1949,6.4
"S3",12/31/1949,6.6
"S4",1/31/1950,6.5
"S5",2/28/1950,6.4
"S6",1/31/1951,3.7
"S7",2/28/1951,3.4




I insert this data using PL/SQL in table.
Data for 1950 and after is ok .
Oracle saved date as 1950,1951  but for 1948,1949 it shows 20048 and 20049.
Please it is very urgent.
Tell me  why data is stored like this and how can I solve it.?

Comment
Watch Question

jrb1senior developer
CERTIFIED EXPERT
Top Expert 2005

Commented:
I'd have to see your PL/SQL to be sure, but I would quess it is 2048 and 2049, not 20048.  That happens when you read a date without the century indicator.  A date of 1/31/48 is 1/31/2048, not 1/31/1948.

Use to_date(datecol,'DD-MON-YYYY') when you insert
jrb1senior developer
CERTIFIED EXPERT
Top Expert 2005

Commented:
you could do:

update yourtable
set yourdate = add_months(yourdate, -1200)
where yourdate >= '1-JAN-2020'
In your case it would be
to_date(datecol,'MM/DD/YYYY') when you insert

Commented:

Assuming you are processing now (2006) then your date, unless you format them as sujected above, will be treated as such:


If the last 2 digits of the year is between 00 and 49 then the first 2 digits are the same as the current year (20)

If the last 2 digits of the year is between 50 and 99 then the first 2 digits are 1 less than the current year (19)


To prove the theory use data of:

"S11",1/31/1748,3.4                 - should result in 2048
"S14",1/31/1850,6.5                 - should result in 1950

Never accept default date conversion - always explicitly code a date format - sathyagirl gave you 2 examples above.

Cheers
JJ

Author

Commented:
Hi,
I am using

to_date(datecol,'MM/DD/YYYY') when I am inserting data.
But still same problem .
Why 1950 works fine and 1948 doesnot?

Commented:


Need more information on how you are accessing the data and the code that inserts it into the table.

You must remember that even if you insert with a to_date conversion you should also convert on the output if it is non-default.

for example:

select to_char(datecol,'MM/DD/YYYY');

can you confirm above ?

Cheers
JJ

Try using RRRR format instead

to_date(datecol,'MM/DD/RRRR') when you insert
jrb1senior developer
CERTIFIED EXPERT
Top Expert 2005

Commented:
When you read the date, are you reading it into a DATETIME field?  You may be losing the year at this time.  I think you are OK on your insert.

Author

Commented:
Hi,
I tried YYYY and RRRR both nothing worked .
I cant post whole code but let me tell you similiar small problem.

i created small pl/sql for eg.

declare
var1 varchar2(10);
var2 varchar2(10);

begin
var2:='5/19/1948';
var1 :=to_date(var2,'MM/DD/YYYY');
insert into test1 values(var1);
var1 :=to_date(var2,'MM/DD/RRRR');
insert into test1 values(var1);
end;


desc test1
----------

col1 date



select to_char(val,'MM/DD/YYYY') from test1
output:
05/19/2048
05/19/2048



why how can i store 1948 as it is?
Thanks
jrb1senior developer
CERTIFIED EXPERT
Top Expert 2005

Commented:
You should be doing this instead:

declare
var1 date;
var2 varchar2(10);

begin
var2:='5/19/1948';
var1 :=to_date(var2,'MM/DD/YYYY');
insert into test1 values(var1);
var1 :=to_date(var2,'MM/DD/RRRR');
insert into test1 values(var1);
end;
jrb1senior developer
CERTIFIED EXPERT
Top Expert 2005

Commented:
Your problem can be shown here:

declare
var1 varchar2(10);
var2 varchar2(10);
begin
var2:='5/19/1948';
var1 :=to_date(var2,'MM/DD/YYYY');
dbms_output.put_line('var1 = ' || var1);
end;
/
var1 = 19-MAY-48

PL/SQL procedure successfully completed.

When you are storing the DATE value of VAR2 as a string in VAR1, you are losing the year.  You can also fix it with:

declare
var1 varchar2(11);
var2 varchar2(10);
begin
var2:='5/19/1948';
var1 :=to_char(to_date(var2,'MM/DD/YYYY'),'DD-MON-YYYY');
insert into test1 values(var1);
end;

Commented:

As I said earlier you need to 'explicitly' set date formats. We all assumed that you were going directly to a datatype of date when assigning/inserting the to_date(datecol,'MM/DD/YYYY').

As you are using an interim step like var1 :=to_date(var2,'MM/DD/YYYY'); - after your explicit to_date, oracle will do an implicit to_char on the assignment to var1, using the default date format ; in short you've wasted a bit of time changing a varchar into a date that oracle changes back to a varchar.

That is further compounded that the insert of var1 into test1 will result in an implicit to_date, again using the default date format.

Whilst you have a lot of examples above that will give you the required result - I hope you understand that on mixed data type assignments oracle will 'silently'(implicitly) execute conversions. In the case of date conversions it will use the default format.

On the same subject you can get implicit conversions on comparisons.

eg.
     select * from dual where to_char(sysdate,'DD-MON-YY') = '25-JUP-56';

should return no rows - we explictly convert sysdate to character before the comparison. So the fact that we have a strange month of JUP gives no problem. However if we try:

     select * from dual where sysdate = '25-JUP-56';

we should get an error (ORA-01843: not a valid month) - because oracle cannot compare a DATE to STRING, so it implicitly converts the string to date, before the comparison. Tip: that could be a way of validating a date that has been sourced from a character field!!

One final thing on the subject :- never to_date a date; whilst the result may be entertaining it is difficult to track the problem in an application containing code like that!

hope that all helps

Cheers
JJ