Datetime Issue based on Different Platforms

Andal · Post by **Andal** » Tue Nov 22, 2005 3:52 am

One of my source column is Timestamp and target column is Date when inserting data into target it giving errror " The string representation of a datetime value is out of range SQLSTATE =22007" . I tried many option like Substrings,ToDate and %DateOut function but still it is not solving the problem. I am getting this error in DB2 OS390, But i am not getting this error in Oracle and Db2Unix.

If i change the date field as Varchar(10), it is working in OS390 and DB2 but failing in Oracle. Is there any way to achieve this.

gradkarthik · Post by **gradkarthik** » Tue Nov 22, 2005 10:34 am

Hi,
you need to convert the timestamp format to the date format using ICONV and OCONV function before you can load them in to either DB2Unix. I am not sure what the format is for OS390 but what ever it is, the conversion has to be done in a transformer stage before loading.
Look in the BASIC Manual for the explanations of ICONV and OCONV. There are also lots of posts on this site which give you an idea. Search using iconv, oconv as keywords.
gradkarthik

Andal · Post by **Andal** » Tue Nov 22, 2005 9:06 pm

Thanks Karthik, But I had already tried using OCONV. Is it necessary to change the date to Datastage and Internal format and then again changing for Database format.

Anyhow , today i will try using the combinations of Iconv and Oconv.

vijay.swarup · Post by **vijay.swarup** » Tue Nov 22, 2005 11:21 pm

hi,
here i can suggest a small solution to your issue...

you try to use " DateTimeStampToInfCLI(<input timestamp>) "
this function converts timestamp to date.
this function is available in DSRoutines/SDK. there are many similar functions of different drivers compatible you can try for apt one...

Regards

chulett · Post by **chulett** » Wed Nov 23, 2005 7:39 am

It sounds like to me that you are trying to come up with some sort of 'universal solution' - some technique to handle Timestamps across all 'platforms' as you call it in exactly the same manner. Can't be done.

Oracle has different requirements than DB2. Other databases throw in other wrinkles. Requirements can also change based on the stage you are using. Best, in my opinion, to standardise on a format for date / time handling like the ISO standard. Basically:

YYYY-MM-DD HH24:MI:SS

Time is optional and can include milliseconds for databases that can handle them, like SQL Server. Then build a set of routines that all developers can leverage to take dates / timestamps and prep them for each database - one for Oracle, one for DB2, etc.