db2 timestamp to date conversion

[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

[chulett]

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'?

[bhaskarjha]

ya , job is server job

[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.

[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.

[DSguru2B]

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.

[ray.wurlod]

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".

[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.