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>>

Attachments

Library

Quick Reference