You are right and I've been attempting to make it work that way.
On my source column meta it comes in as a Decimal( 8 ), my destination is a Date, in the SQL that datastage auto generates it is using TO_DATE()
Code: Select all
INSERT INTO ....
VALUES (...
MY_DATE_COLUMN=TO_DATE(:73, 'DD-MON-YYYY')
....
)
And in my transformer I process everything through this routine .
Code: Select all
If Arg1 > 0 And Iconv(Arg1,"DYMD":@VM:"MCN") Then Ans = Oconv(Iconv(Arg1,"DYMD":@VM:"MCN"), "D-DMY[2,A3,4]")
(If the data is zero I should treat as null. And I'm a newbie so I'm still working on how to properly test for empty or null values in the routine but I do know that on clean data it does format the numbers correctly)
and when I run the job this is the error I get
Code: Select all
Facility_Table_Load..Transformer_2: At row 1, link "DSLink4", while processing column "MY_DATE_COLUMN"
Value treated as NULL
Attempt to convert String value "08-NOV-2007" to Date type unsuccessful
So I'm just really scratching my head over this one as to how to properly transform a number 20120808 in a way that I can insert it into a date column.