DATEDIFF function resulted in an overflow on one instance not the other

Question

Post reply

DATEDIFF function resulted in an overflow on one instance not the other

SSSolice

SSCommitted

Points: 1907
More actions
August 5, 2010 at 9:00 am

#227504

Any idea why I get the "datediff function resulted in an overflow... error on one instance of SQL Server 2008 and not the other.
Instance A - operational db
Instance B - reporting db (transactional replication from 'A')
When I use the operation data store to reference a particular table I do not get the error. When I point the query to use the reporting copy of that table I then get the overflow error.
ABS(CONVERT(BIGINT,DateDiff(SS, s.dteAssignEndTS, c.dteFirstAssigned)))
Any help is greatly appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

HowardW One Orange Chip Points: 29109 More actions · Answer 1

What are the data types of the two input columns in your datediff?

Lowell SSC Guru Points: 323518 More actions · Answer 2

the error is related to the actual data being tested/datediffed. one instance has data that is out of range.

when you use datediff with seconds, there can only be something like a 67 year gap; after that the #seconds is greater than an int.

switch to datediff(minute and you'll fix the issue...if you are datediffing something that is 90 years, you don't need the # seconds difference anyway.

select DateDiff(SS,getdate(),'1900-01-01')

Msg 535, Level 16, State 0, Line 1

Difference of two datetime columns caused overflow at runtime.

do this instead: get minutes, multiply by 60 for seconds.

ABS(CONVERT(BIGINT,DateDiff(minute, s.dteAssignEndTS, c.dteFirstAssigned) * 60))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SSSolice SSCommitted Points: 1907 More actions · Answer 3

The data type is datetime.

When I use ABS(CONVERT(BIGINT,DateDiff(mi, s.dteCrtTS, s.dteAssignEndTS)*60))

I get: Arithmetic overflow error converting expression to data type int.

HowardW One Orange Chip Points: 29109 More actions · Answer 4

You would need to convert the result of the datediff to a BIGINT before multiplying by 60...these must be long assignments!

E.g.:

ABS(CONVERT(BIGINT,DateDiff(mi, s.dteCrtTS, s.dteAssignEndTS))*60)

If both data types are datetime, then there must be different data in the two environments...

Lowell SSC Guru Points: 323518 More actions · Answer 5

doh i should have tested that: wrapped the datediff evaluation with the convert first, sorry:

select ABS(CONVERT(BIGINT,(DateDiff(minute, getdate(), '1900-01-01'))) * 60),

ABS(CONVERT(BIGINT,(DateDiff(minute, s.dteCrtTS, s.dteAssignEndTS)))*60)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

HowardW One Orange Chip Points: 29109 More actions · Answer 6

Actually, you could have the same data, but you're for example using this function in a where clause along with other criteria and one execution plan is hitting a row that causes the overflow and one is not.

SSSolice SSCommitted Points: 1907 More actions · Answer 7

Thanks for catching that. I should've spotted it. This works.

ABS(CONVERT(BIGINT,(DateDiff(minute, s.dteCrtTS, s.dteAssignEndTS)))*60)

Wish I had time to dig into what differences are causing this.

Thanks Guys!