DB2 Datetime to Date

jpr196 · Post by **jpr196** » Mon Dec 18, 2006 9:58 am

Hi again everyone,

I have a delivered job that I cannot figure out. I have done many searches on this issue but have found most of the results relevant for formatting issues.

Anyway, we're on a db2 database first of all. Basically, on one of the lookups a datetime field is being used to populate the target table which has a date field. The delivered job uses Timestamp for both the lookup and the target field. When the target table datatype is changed to date, the job doesn't abort but doesn't load any of the records with a valid date.

I have tried converting the datetime field to a string, truncating the "time", and then converting it back to a date, but that didn't work. I've also tried using the OConv and IConv functions. The error I typically get is cannot convert string to Date error when loading the target table.
The format we're trying to get the date into is YYYY-MM-DD.
Anyone think they can provide some insight? Thanks!

kcbland · Post by **kcbland** » Mon Dec 18, 2006 10:02 am

DB2 plugin works differently than Oracle or ODBC stages. If the datatype is DATE, the plugin will silently do an ICONV coming and OCONV going. Change the metadata in the job to TIMESTAMP and see if your problem goes away. In the future make sure you always change your metadata.

Otherwise, just put OCONV on all derivations in the first Transformer stage after the DB2 plugin and ICONV on the last Transformer stage prior to a DB2 plugin.

jpr196 · Post by **jpr196** » Mon Dec 18, 2006 2:31 pm

kcbland wrote:DB2 plugin works differently than Oracle or ODBC stages. If the datatype is DATE, the plugin will silently do an ICONV coming and OCONV going. Change the metadata in the job to TIMESTAMP and see if your problem goes away. In the future make sure you always change your metadata.

Otherwise, just put OCONV on all derivations in the first Transformer stage after the DB2 plugin and ICONV on the last Transformer stage prior to a DB2 plugin.

Hi, the metadata in the job is Timestamp for all the date columns, regardless if it's actually Date or DATETIME in DB2. Also, using Field ICONV and OCONV, I am able to get my date in the format that I want. Everything goes through fine until it gets to loading the target. There, it sees my field as a string and won't convert it to a date. Is there a way to convert a string to a db2 date field?

DSguru2B · Post by **DSguru2B** » Mon Dec 18, 2006 2:36 pm

First of all make sure its in the correct format (YYYY-MM-DD HH:MM:SS.nnnnnn). Even if you dont have the franctional seconds part, its ok. DB2 will add that during insert. Make sure you have a single space between the date part and time part of the timestamp. If all of that is in place, then try changing the sql type from timestamp to varchar within DataStage and then try loading it.

ray.wurlod · Post by **ray.wurlod** » Mon Dec 18, 2006 3:01 pm

There are Transforms for extracting the date portion of a Timestamp, or you can use

Code: Select all

Field(InLink.TheTimestamp, " ", 1, 1)

or

Code: Select all

Left(InLink.TheTimestamp, 10)

etc.

jpr196 · Post by **jpr196** » Mon Dec 18, 2006 3:18 pm

ray.wurlod wrote:There are Transforms for extracting the date portion of a Timestamp, or you can use

Code: Select all

Field(InLink.TheTimestamp, " ", 1, 1)

or

Code: Select all

Left(InLink.TheTimestamp, 10)[ ...[/quote]

Hi Ray,

I tried the Field function and it does truncate my timestamp fine leaving the date how I want it: YYYY-MM-DD.  However, when it attempts to load this value into the db2 target, I get the error: 
Attempt to convert String value "2006-02-13" to Date type unsuccessful
I also got this error if I use timestamp as the datatype.

ray.wurlod · Post by **ray.wurlod** » Mon Dec 18, 2006 3:34 pm

That's because the DB2 stage (idiosyncratically) wants an internal format date. You achieve this with an Iconv() function.

Code: Select all

Iconv(Field(InLink.TheTimestamp, " ", 1, 1), "DYMD")

DSguru2B · Post by **DSguru2B** » Mon Dec 18, 2006 3:56 pm

Internal format will work only if its defined as Date in the target database. If its timestamp then internal format wont work. Try Ray's suggestion, which is the same as Ken pointed out. If that doesnt work that means you have to supply timestamp.

jpr196 · Post by **jpr196** » Mon Dec 18, 2006 4:28 pm

DSguru2B wrote:Internal format will work only if its defined as Date in the target database. If its timestamp then internal format wont work. Try Ray's suggestion, which is the same as Ken pointed out. If that doesn ...

Yep, Ray's suggestion did work and you are correct about it having to be the Date datatype in the target database. Thanks for all the help once again!

manish1005 · Post by **manish1005** » Wed Dec 20, 2006 2:14 am

thanks guys......I also faced the same problem, and chanced upon this thread after wasting half a day trying n number of combinations and searching for more DS internal functions.....

this site really rocks!

thanks again
-manish