DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Date Format

  1. #1
    Join Date
    Sep 2001
    Posts
    46

    Question

    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

  2. #2
    Join Date
    Sep 2000
    Posts
    19
    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

  3. #3
    Join Date
    Sep 2001
    Posts
    46
    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

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Sep 2000
    Posts
    19
    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

  6. #6
    Join Date
    Sep 2001
    Posts
    46
    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
  •  


Click Here to Expand Forum to Full Width