Page 1 of 1
Invalid timestamp value
Posted: Wed Mar 03, 2010 3:44 pm
by jagadam
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.
Posted: Wed Mar 03, 2010 4:35 pm
by anbu
Are you extracting "2009-10-10-23 24:00:00.000000" from a char field in DB2?
Posted: Wed Mar 03, 2010 4:54 pm
by jagadam
No, The field in the Db2 is a timestamp(26,6).
Posted: Wed Mar 03, 2010 6:43 pm
by chulett
How the heck did your timestamp end up with a date value of "2009-10-10-23" in it?
Posted: Wed Mar 03, 2010 9:56 pm
by jagadam
Hi
Sorry it should read like "2009-10-23"
Thanks
Naveen.
Posted: Thu Mar 04, 2010 6:24 am
by chulett
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.
Posted: Thu Mar 04, 2010 5:14 pm
by jagadam
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.
Posted: Thu Mar 04, 2010 5:15 pm
by ray.wurlod
24:00:00 is not valid in DB2. Only 00:00:00 through 23:59:59 are valid.
Posted: Thu Mar 04, 2010 8:56 pm
by chulett
Best to get that checked out, either by a DB2 DBA / support or DataStage support. Or both... one stop shopping.
Posted: Fri Mar 05, 2010 1:09 pm
by jagadam
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.
Posted: Fri Mar 05, 2010 3:15 pm
by chulett
No clue, hence the suggestion to check with your support provider.
Posted: Tue Jun 15, 2010 1:16 pm
by jagadam
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
Posted: Tue Jun 15, 2010 1:31 pm
by anbu
Code: Select all
If Index(Fld,"24:00:00") = 1 Then DateFromDaysSince(1,Fld[1,10]) :" 00:00:00" Else Fld
Posted: Wed Apr 18, 2012 12:54 pm
by DWS
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..
Posted: Wed Apr 18, 2012 2:41 pm
by rahcomp
- Could you cast the source column to varchar / date and try extracting ?