Page 1 of 1
db2 timestamp to date conversion
Posted: Fri Jul 21, 2006 6:59 am
by bhaskarjha
hi
I am having source as db2 udb stage and target as db2 udb stage, with database as db2.
in source i am having a column with metadata as timestamp 26, scale 6, trying to convert it into a target column with metadata date 10.
its gives me following error
"Value treated as NULL
Attempt to convert String value "2003-07-26 00:00:00.000000" to Date type unsuccessful"
i also tried with "timestamp.to.date(sourcecoulmn[1,19])" but its then rejects the row
Re: db2 timestamp to date conversion
Posted: Fri Jul 21, 2006 7:04 am
by chulett
bhaskarjha wrote:i also tried with "timestamp.to.date(sourcecoulmn[1,19])" but its then rejects the row
That looks like a PX derivation, you sure your Job Type is 'Server'?
Posted: Fri Jul 21, 2006 7:15 am
by bhaskarjha
ya , job is server job
Posted: Sat Jul 22, 2006 12:45 am
by ray.wurlod
You are using one of the SDK functions. Take a look at its source code and see what it expects. The SDK functions have their own idiosyncratic definition for the format of a timestamp. Copy that routine, and adapt it to your own requirements.
Posted: Sat Jul 22, 2006 7:08 am
by chulett
No, they're
not. There are no Server SDK routines or Transforms with dots in their names. This is more like the typical PX function naming style, from what little I've seen, hence the question.
If it's truly a
Server job then it's a home grown routine. In any case, check the code and the internal documentation it should have to see what it is expecting. See if it is even appropriate for what you are doing. I doubt it as you are trying to populate a
date field, not a timestamp.
Substring at 10 so you just get the date portion. Use IConv only on that as the DB2 stage expects dates in internal format. See if that works.
Posted: Sat Jul 22, 2006 9:22 am
by DSguru2B
chulett wrote:Substring at 10 so you just get the date portion. Use IConv only on that as the DB2 stage expects dates in internal format. See if that works.
That is your solution right there.
Posted: Sat Jul 22, 2006 6:41 pm
by ray.wurlod
chulett wrote:No, they're not. There are no Server SDK routines or Transforms with dots in their names. This is more like the typical PX function naming style, from what little I've seen, hence the question.
Not if transcribed inaccurately by the OP. The PX functions also don't have dots in their names; the Modify stage functions use underscores, while the Transformer stage functions use "camel case".
Posted: Sat Jul 22, 2006 8:53 pm
by chulett
Ok, point conceeded - if transcribed in a
highly inaccurate manner.
Here are some Server SDK samples:
Code: Select all
DateGenericToTimeStamp
DateTimeStampToOraOCI
DateTimeStampToOraOCIWithTime
They all take an "SDK timestamp" which is "YYYYMMDD HH:MI:SS.TTT" format and something I never use. And I'm obviously misremembering what PX functions look like and didn't bother to look them up to check.
