Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 22 Apr 2002 @ 13:58:12 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Converting 4-digit dates to standard date
 
From:   Geoffrey Rommel

  We have a table with seven kinds of immunization dates of year and month only in separate columns. Example -  


  IMMUN_EXPIRE_MM BYTEINT FORMAT 'ZZ99'
IMMUN_EXPIRE_YY BYTEINT FORMAT 'ZZ99'
 


  Now we are required to maintain such dates as a DATE column - at our site this is still YY/MM/DD.  


  We want to use 01 as the Day, concatenate it with the year and month from the old table, and insert it into a new table where immunization dates are defined as DATE. The old table has 50 million rows. We are still V2R3.0.3.  



The following code avoids casting and takes care of the "100 means 2000" problem mentioned by Paul Johnson. It works on V2R04.01.00 and should also work on your version.

insert into new_table
 select
  case
    when immun_expire_yy >= 50
      then (immun_expire_yy * 10000) + (immun_expire_mm * 100) + 1
    else ((immun_expire_yy + 100) * 10000) + (immun_expire_mm * 100) + 1
  end
 from old_table...;

I would also recommend making the default format for all dates ANSI ('YYYY-MM-DD'). It seems odd to most users at first, but it has saved us a lot of headaches on our system.


--wgr



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016