Archives of the TeradataForum

Message Posted: Mon, 22 Apr 2002 @ 13:24:36 GMT

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

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'

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?

John Balchunas
Jefferson County Public Schools

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