Invalid timestamp value

jagadam · Post by **jagadam** » Wed Mar 03, 2010 3:44 pm

Hi All,

Iam trying to run a job which extracts data from db2 using a Db2 Plugin stage and writing to a dataset.

While doing this I am getting the following error.

"Invalid timestamp value in setOutputFieldAs TimeStamp() for field'3' in output dataset '0'. Time stamp string = 2009-10-10-23 24:00:00.000000, format string = %yyyy-%mm-%dd %hh:%nn:%ss.6."

The input field is a timestamp and iam not using any conversions.

Thanks in advance for your help.

Thanks
Naveen.

anbu · Post by **anbu** » Wed Mar 03, 2010 4:35 pm

Are you extracting "2009-10-10-23 24:00:00.000000" from a char field in DB2?

jagadam · Post by **jagadam** » Wed Mar 03, 2010 4:54 pm

No, The field in the Db2 is a timestamp(26,6).

chulett · Post by **chulett** » Wed Mar 03, 2010 6:43 pm

How the heck did your timestamp end up with a date value of "2009-10-10-23" in it?

jagadam · Post by **jagadam** » Wed Mar 03, 2010 9:56 pm

Hi

Sorry it should read like "2009-10-23"

Thanks
Naveen.

chulett · Post by **chulett** » Thu Mar 04, 2010 6:24 am

OK, then let's move on to the time portion. Ignoring the milliseconds, is it really coming over as "24:00:00"? I do not believe that is a valid time value in any system... 23:59:59 + 1 = 00:00:00 AFAIK.

jagadam · Post by **jagadam** » Thu Mar 04, 2010 5:14 pm

Hi,

Thanks for your comments.
What you said is a valid point. when I filtered the data excluding the dates with time stamp "24:00:00".
Datastage passed the records. i.e the job ran fine with out any warnings.

But the source has the data with the above mentioned timestamp.

Is it a bug on the source side...? Is it valid to store the data as "24:00:00" in db2.

Thanks for your inputs.

Thanks
Naveen.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 04, 2010 5:15 pm

24:00:00 is not valid in DB2. Only 00:00:00 through 23:59:59 are valid.

chulett · Post by **chulett** » Thu Mar 04, 2010 8:56 pm

Best to get that checked out, either by a DB2 DBA / support or DataStage support. Or both... one stop shopping.

jagadam · Post by **jagadam** » Fri Mar 05, 2010 1:09 pm

Hi,

When i ran the query using Db2 client the out put we are getting is "2009-10-24 00:00:00" but when running the same query in datastage using db2 plug in we are getting it as "2009-10-23 24:00:00".

Is it a problem with the db2 plug in connection..? Isn't it set up correctly..?

Thanks
Naveen.

chulett · Post by **chulett** » Fri Mar 05, 2010 3:15 pm

No clue, hence the suggestion to check with your support provider.

jagadam · Post by **jagadam** » Tue Jun 15, 2010 1:16 pm

Hi,

I opened a PMR with IBM on this Time format and they said that "24:00:00" is a valid db2 time format. So dont know how to handle this in datastage as I need to convert this into the next day date.
for example if it is

2010-05-12 24:00:00 then it needs be converted into 2010-05-13 00:00:00

Thanks

anbu · Post by **anbu** » Tue Jun 15, 2010 1:31 pm

Code: Select all

If Index(Fld,"24:00:00") = 1 Then DateFromDaysSince(1,Fld[1,10]) :" 00:00:00" Else Fld

DWS · Post by **DWS** » Wed Apr 18, 2012 12:54 pm

Old topic, but just stumbled upon this when searching for a resolution to the same problem. I found a different / better solution for our purposes though.

Since DS chokes on the DB2 date on attempted load, I found that this 'fixed' the issue for me...

Within the DB2 query I placed this:

SELECT CASE WHEN HOUR(BAD_DTTM) > 23 THEN BAD_DTTM - 1 MICROSECOND ELSE BAD_DTTM END AS GOOD_DTTM FROM TABLE;

Anyways, figured I'd share..

rahcomp · Post by **rahcomp** » Wed Apr 18, 2012 2:41 pm

- Could you cast the source column to varchar / date and try extracting ?