Invalid timestamp value
Moderators: chulett, rschirm, roy
Invalid timestamp value
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.
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.
NJ
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.
Last edited by chulett on Thu Mar 04, 2010 8:55 pm, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
NJ
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
Code: Select all
If Index(Fld,"24:00:00") = 1 Then DateFromDaysSince(1,Fld[1,10]) :" 00:00:00" Else Fld
You are the creator of your destiny - Swami Vivekananda
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..
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..