TimeStamp problem with DB2UDB Stage

[kaps]

Hi

One of my source column in my source file is in this format yyyy/mm/dd hh24:mi:ss.000000 and my target is in db2udb with a column of datatype timestamp.

If I just pass on the column I get this error...
Value treated as NULL
Attempt to convert String value " " to Timestamp type unsuccessful


If I do the following in the thransformer ...
ICONV(L1.END_DT,"D-YMD[4,2,2]"):" ":ICONV(L1.END_DT,"MTHS")

I get this error...
Value treated as NULL
Attempt to convert String value " 0" to Timestamp type unsuccessful

Please share some ideas...

Thanks

[us1aslam1us]

Do you have any NULL values in the timestamp column from the source?

Sam

[kaps]

No. I don't...I only have two records in my source

Thanks

[chulett]

You can't just IConv the whole field, once for date and once for time. Break the field up, substring or Field command, and do what you are doing on the two pieces.

[DSguru2B]

Craig is right. ICONV/OCONV operations are to be performed on both date and time parts seperately. In your case, the time looks fine, its just the date part you need to be worried about. Just do this

Code: Select all

OCONV(ICONV(LEFT(L1.END_DT,10),"D/YMD[4,2,2]"),"D-YMD[4,2,2]"):Right(L1.END_DT,16)

Your date part just needs a '-' instead of '/'. You can also use

Code: Select all

EREPLACE(L1.END_DT,"/","-")

[ray.wurlod]

Even ICONV(L1.END_DT,"D-YMD[4,2,2]"):" ":ICONV(L1.END_DT,"MTHS") will not give you what you want. What is the data type of END_DT ?

But there is no internal format of a timestamp; so that ICONV() questions are irrelevant. You must construct a timestamp as a string of 19 or more characters, as expected by current DB2 database settings.

[kaps]

I learnt an easier way of doing this. Just need to change the data type of the DB2UDB stage to char from timestamp and pass the value as it is.
It takes care of everything. Don't need to do iconv or oconv...
This will work only if the dates and timestamps are in db2udb format.

Thanks