Page 1 of 1
Datetimeoverflow error
Posted: Mon Aug 01, 2005 11:41 pm
by poornimajayan
Hi,
I am using DB2 as my target database.I am appending the timepart from the source datetime field with the currentdate like:
DSJobStartDate:' ':Substring(datefield,12,19)
If I do like
DSJobStartDate:' ':'00:00:00' the job is running fine.
But for the first one, it is giving the datetimeoverflow error.
Please suggest how to go with this.
Thanks,
Poornima
Posted: Mon Aug 01, 2005 11:45 pm
by amsh76
please make sure the time part is in correct format...and the quickest solution I can suggest is change the datatype in API stage to Char(19).
Can you please give example of time field.
HTH
Posted: Mon Aug 01, 2005 11:52 pm
by poornimajayan
Hi,
My datetime field in the target is of the format 1900-01-01 00:00:00.
Posted: Tue Aug 02, 2005 12:05 am
by ray.wurlod
Substrings(x, 12, 19) returns a 19 character string, that may include fractional seconds if present. Did you want Substrings(x, 12, 9)?
Posted: Tue Aug 02, 2005 12:08 am
by amsh76
Man good catch Ray, How did I miss that...
![Rolling Eyes :roll:](./images/smilies/icon_rolleyes.gif)
Posted: Tue Aug 02, 2005 1:12 am
by poornimajayan
Hi Ray/Amsh.Thanks.
Even still the error persists.For a safer side, I put
Substrings(
DSJobStartDate:' ':Substring(datefield,12,9) ,1,19)
Still it remain the same.
As I mentioned, this work if I hardcode '00:00:00' for that last part instead of substring.But then my requirement is failing.
Thanks,
Poornima
Posted: Tue Aug 02, 2005 7:25 am
by amsh76
Hey Poornima,
What is your data type ? DATE or TIMESTAMP ?
The other option you can try is converting the date and time in Internal format and then try to insert into DB2 table, via API stage.
Let us know...if it works.
Thanks.
Posted: Tue Aug 02, 2005 7:41 am
by roy
Hi,
Why not use the subtring operator [start,length] ????
link.col[1,10] : " 00:00:00"
should give you "2005-08-02 00:00:00" for today as date using YYYY-MM-DD format.
Doesn't that maco return an internal date format?
Why not use the start timestamp macro?
IHTH,
Posted: Wed Aug 03, 2005 7:44 am
by peterbaun
Hi Poornima,
Will changing the expression to
Substrings(DSJobStartDate:' ':Substring(datefield,12,8) ,1,19)
make any change ?
(Changed the number of positions from 9 to 8 in the inner substring function.)
In any case - send the result of the substring to a flat file so you can see what you actually try to insert into the database. It often reveals the problem.
Regards
Peter
Posted: Wed Aug 03, 2005 7:59 am
by peterbaun
hello again -
something went wrong in the copy-paste.
To be clear -
Substrings(DSJobStartDate : ' ' : Substring(datefield, 12, 8), 1, 19)
Posted: Wed Aug 03, 2005 8:01 am
by peterbaun
I give up -
DSJobStartDate[1,10] : ' ' : datefield[12,8]
Posted: Wed Aug 03, 2005 10:02 am
by gpatton
When using DB2 define the datetime variable as varchar 26
Then use the format 00:00:00.00000 for the time portion of the datetime string.
Posted: Wed Aug 03, 2005 4:20 pm
by ray.wurlod
peterbaun, you need to check Disable Smilies in this post otherwise 8) will deliver the "cool" emoticon.
You can go back and edit your post and still accomplish this.