-
I recently migrated a database from MS Access with a date column specified as MM/DD/YYYY. After migrating to Oracle I noticed the date column shows as 2098 instead of 1998. I tried using 'RRRR' instead of 'YYYY' but it still shows as 2098. Is there a work around for this problem? I'd hate to go thru the migration again.
thanks!!
Randy
-
Hey Randy,
The oracle database stores dates as a 'Julian' date, so if it shows up as the year 2098 it's more than just an NLS_DATE_FORMAT issue. If there isn't much data I would try the migration again, with the NLS-DATE_FORMAT set to 'RRRR' just in case. Other wise you could come up with a little pl/sql block that could update the dates for you.
Hope this helps,
Frank
-
Originally posted by fgping
Hey Randy,
The oracle database stores dates as a 'Julian' date, so if it shows up as the year 2098 it's more than just an NLS_DATE_FORMAT issue. If there isn't much data I would try the migration again, with the NLS-DATE_FORMAT set to 'RRRR' just in case. Other wise you could come up with a little pl/sql block that could update the dates for you.
Hope this helps,
Frank
Hi Frank,
I've made some changes to the table after the migration and would hate to do it again. How would I update just the year and not change the MM/DD?
Thanks,
Randy
-
You could always update the value:
Code:
SQL> drop table xyz;
Table dropped.
SQL> create table xyz (x date);
Table created.
SQL> insert into xyz values (to_date('20980501','yyyymmdd'));
1 row created.
SQL> commit;
Commit complete.
SQL> select * from xyz;
X
---------
01-MAY-98
SQL> alter session set nls_date_format="mm/dd/yyyy";
Session altered.
SQL> select * from xyz;
X
----------
05/01/2098
SQL> select add_months(x,-1200) from xyz;
ADD_MONTHS
----------
05/01/1998
SQL> update xyz set x = add_months(x,-1200);
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from xyz;
X
----------
05/01/1998
Jeff Hunter
-
Hi Randy,
Man, I had to open my mouth. I didn't test this, but it should give you a good blue print at least:
CLEAR BUFFER
set echo off;
set termout on;
set feedback off;
set serveroutput on size 50000;
DECLARE
PK ;
DT date;
cursor cur0 is
select , to_char(add_months(, -1200) 'DD-MON-YYYY') from
where = 'CONDITION';
begin
for cur0_rec in cur0 loop
PK := cur0_rec.;
DT := cur0_rec.;
update set = DT
where = PK;
dbms_output.put_line('cHANGED DATE TO .. ' ||);
end loop;
commit;
end;
/
spool off;
***** Replace everything between '<' and '>' with actual table/column names. The way I set this up it will subtract 100 yrs . from whatever date, so you'll have to come up with your own constraint in the where clause so that it doesn't subtract 100yrs from every record in the table. Again, I didn't test this, so I would try it out on a dummy table first.
Good luck,
Frank
-
Ok, I have a good idea on what I have to do. Thanks for your help!
Randy
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|