Hi Folks,

I'm getting a very strange error when adding days to a timestamp using the following SQL:

INSERT INTO table1
VALUES((CURRENT_TIMESTAMP + (24 * 86400000)))

This works fine with any number up to 24. If I change the number of days to add to 25 or higher the returned timestamp is wrong.

Adding 24 days to now gives me 16/03/2012 3:19:11 PM (formatted to Australian date ie dd/mm/yy)

Adding 25 days gives me 27/01/2012 10:17:50 PM instead of 17/03/2012 10:17:50 PM

Any idea what to problem might be?

Thanks in advance, Mark Gibson

Whoops, should have added it is Version 4.30 (4.30 Build 7)

Mark


I guessing that you're exceeding a data type size somewhere, probably the integer you're generating by multiplying days and milliseconds a day and its wrapping round.

Its a bit cludgey but try

CAST(
CAST((CURRENT_DATE+25) AS VARCHAR(10))
+' '+
CAST(CURRENT_TIME AS VARCHAR(8))
AS TIMESTAMP)


Roy Lambert [Team Elevate]

Mark,

<< I'm getting a very strange error when adding days to a timestamp using
the following SQL: >>

Raul is 100% correct - you've got an Integer wrapping issue. Use this SQL
instead:

INSERT INTO table1
VALUES((CURRENT_TIMESTAMP + (25 * CAST(86400000 AS LARGEINT))))

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks so much guys, I'll give this a try after work tonight.

Cheers, Mark

"Tim Young [Elevate Software]" wrote:

Mark,

<< I'm getting a very strange error when adding days to a timestamp using
the following SQL: >>

Raul is 100% correct - you've got an Integer wrapping issue. Use this SQL
instead:

INSERT INTO table1
VALUES((CURRENT_TIMESTAMP + (25 * CAST(86400000 AS LARGEINT))))

--
Tim Young
Elevate Software
www.elevatesoft.com