Archives of the TeradataForum
Message Posted: Mon, 22 Apr 2002 @ 13:24:36 GMT
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'
We also have similar ones for chicken pox, TB, etc.
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.
When I try to concatenate the fields (using BTEQ thru the mainframe), I keep getting an invalid date error immediately. It's definitely the coding that is the problem. The exclamation point is the concatenation character on our Bull mainframe.
CAST ( (SUBSTRING(IMMUN_EXPIRE_YY FROM 3 FOR 2)(BYTEINT, FORMAT '99')) !!'/'!! (SUBSTRING(IMMUN_EXPIRE_MM FROM 3 FOR 2)(BYTEINT, FORMAT '99')) !!'/'!! '01' AS DATE)
When I use a SELECT with only the two substring/concatenation and no CAST, I get data that looks like a date, for example, 99/12/01.
But when I try to insert it into the column defined as DATE, an error code pops up indicating invalid date (2666).
When CAST AS DATE is added as indicated above, 2666 again happens.
I have tried many iterations with CAST and without it to no avail.
Does anyone see any obvious errors or another approach?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|