Archives of the TeradataForum
Message Posted: Mon, 22 Apr 2002 @ 13:58:12 GMT
| 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
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
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.