Datetimeoverflow error

[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

[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

[poornimajayan]

Hi,
My datetime field in the target is of the format 1900-01-01 00:00:00.

[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)?

[amsh76]

Man good catch Ray, How did I miss that...

[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

[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.

[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,

[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

[peterbaun]

hello again -

something went wrong in the copy-paste.
To be clear -

Substrings(DSJobStartDate : ' ' : Substring(datefield, 12, 8), 1, 19)

[peterbaun]

I give up -

DSJobStartDate[1,10] : ' ' : datefield[12,8]

[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.

[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.