"Arithmetic overflow error" but data calculated as expected.

Question

Post reply

"Arithmetic overflow error" but data calculated as expected.

mr.databases

SSC Enthusiast

Points: 141
More actions
December 14, 2015 at 1:43 pm

#399470

Hi Group!
I am trying to calculate the number of days between 2 given dates - received date and determined date.
If the determined date is null, then instead I would like to calculate the numbr of days between received date and today.
I have written the following statement:
CASE WHEN det_dte IS NULL THEN CAST({ fn NOW() } - rec_dte AS int) ELSE CAST(det_dte - rec_dte AS int) END
rec_dte and det_dte are both data type datetime.
The query runs and the number of days is calculated exactly as expected, but I also get the error:
"Arithmetic overflow error converting expression to data type datetime"
I cannot see where I am actually converting anything to datetime.....only to integer.
Can you see where I am going wrong?
Thanks in advance for any advice you can offer
cheers,
Meg

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

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

The error is somewhere else in the query.

Or - either det_dte or rec_dte is not datetime.

_____________
Code for TallyGenerator

LinksUp SSCertifiable Points: 6526 More actions · Answer 2

mr.databases (12/14/2015)
Hi Group!
CASE WHEN det_dte IS NULL THEN CAST({ fn NOW() } - rec_dte AS int) ELSE CAST(det_dte - rec_dte AS int) END
rec_dte and det_dte are both data type datetime.
The query runs and the number of days is calculated exactly as expected, but I also get the error:
"Arithmetic overflow error converting expression to data type datetime"
I cannot see where I am actually converting anything to datetime.....only to integer.
Can you see where I am going wrong?

Don't do regular arithmetic on temporal data types. Use DateDiff to get the difference you need. So your statement becomes:

DateDiff(dd, case when det_dte is null GetDate() else det_dte end, rec_Dte)

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Sergiy SSC Guru Points: 110208 More actions · Answer 3

LinksUp (12/14/2015)
Don't do regular arithmetic on temporal data types. Use DateDiff to get the difference you need. So your statement becomes:
DateDiff(dd, case when det_dte is null GetDate() else det_dte end, rec_Dte)

It should be another way around (and a bit simpler 🙂 )

DateDiff(dd, rec_Dte, ISNULL(det_dte, GetDate()) )

_____________
Code for TallyGenerator

Leo.Miller SSChampion Points: 13051 More actions · Answer 4

I've tested your code and it works without returning the overflow error. Are you sure the error is in this line? Could you post more of the code?

Minor correction to the code to get it to return the same result: DateDiff(dd, rec_Dte, case when det_dte is null then GetDate() else det_dte end)

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

mr.databases SSC Enthusiast Points: 141 More actions · Answer 5

Bingo!

I used:

DateDiff(dd, rec_Dte, case when det_dte is null then GetDate() else det_dte end)

Results are returned as expected with no error.

Thank you very much!

mr.databases SSC Enthusiast Points: 141 More actions · Answer 6

This also works, and as pointed out, is a little simpler:

DATEDIFF(dd, rec_dte, ISNULL(det_dte, GETDATE()))

Thanks again! 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

LinksUp (12/14/2015)
mr.databases (12/14/2015)
Hi Group!
CASE WHEN det_dte IS NULL THEN CAST({ fn NOW() } - rec_dte AS int) ELSE CAST(det_dte - rec_dte AS int) END
rec_dte and det_dte are both data type datetime.
The query runs and the number of days is calculated exactly as expected, but I also get the error:
"Arithmetic overflow error converting expression to data type datetime"
I cannot see where I am actually converting anything to datetime.....only to integer.
Can you see where I am going wrong?
Don't do regular arithmetic on temporal data types. Use DateDiff to get the difference you need. So your statement becomes:
DateDiff(dd, case when det_dte is null GetDate() else det_dte end, rec_Dte)

That depends 😉

Regular arithmetic on temporal datatypes has its uses[/url].

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden