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
TimeStamp problem with DB2UDB Stage
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
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
Your date part just needs a '-' instead of '/'. You can also use
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)
Code: Select all
EREPLACE(L1.END_DT,"/","-")
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.