Blanks/Null Values in the date field
Posted: Fri May 11, 2007 12:12 am
Hi All,
My source data contains date in the format "20060101" which I convert and insert into the target table using the following functions:
This is working fine in all the jobs except One where the source column contains Blank/Nulls in the date column. I have to default the date to "99991231" for which I modified the above function and used the following function in the column derivation in a transformer just before insering in the target table :
But its not working, the Trim function is also not workin and all data is rejected giving the following Warning message " ORA-01843: not a valid month". what should I do to handle these blanks and Nulls so that I can insert the default date instead of these blanks.
Any one with any ideas Please help.
Thanks in Advance.
My source data contains date in the format "20060101" which I convert and insert into the target table using the following functions:
as suggested in another thread some days back.Oconv(Iconv(InLink.TheDate,"DYMD"),"D-YMD[4,2,2]") : " 00:00:00"
This is working fine in all the jobs except One where the source column contains Blank/Nulls in the date column. I have to default the date to "99991231" for which I modified the above function and used the following function in the column derivation in a transformer just before insering in the target table :
If (IsNull(Trim(Link.Column)) Or Link.Column= 0 )
Then Oconv(Iconv('99991231', "DYMD"),"D-YMD[4,2,2]") : "00:00:00"
Else Oconv(Iconv(Link.Column, "DYMD"),"D-YMD[4,2,2]") : " 00:00:00"
But its not working, the Trim function is also not workin and all data is rejected giving the following Warning message " ORA-01843: not a valid month". what should I do to handle these blanks and Nulls so that I can insert the default date instead of these blanks.
Any one with any ideas Please help.
Thanks in Advance.