ERROR - Arithmetic overflow error converting expression to data type datetime.

Question

Hi all...

I'm having trouble to calculate the datediff in hours using two fields, the script is shown below... why is this error occuring

select datediff(hour,[date1], [date2)])
from #temp

Msg 8115, Level 16, State 2, Line 15
Arithmetic overflow error converting expression to data type datetime.

Answer 1

Sam233

 

Please post some sample data..

 

VT

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 2

Can you show us the data? select datediff(hour,GETDATE(), GETDATE()-10)

 

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

What are the data types of date1 and date2?

As a guess I would guess that they are varchar, and there bad dates like 2011-09-30. It could also be an issue with data in D-M-Y format, and your date-format setting is MDY.

You can use

SELECT * FROM #temp WHERE isdate(date1) = 0 OR isdate(date2) = 0

to find bad data.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Clearly, by the error, either [date1] or [date2] (or both) are not of type DATETIME.

 

DECLARE @date1 DECIMAL(10,2) = 20111005.8;
DECLARE @date2 DATETIME = '20110901';

SELECT DATEDIFF(HOUR, @date1, @date2);

/*Results

Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type datetime.
*/

---

MG.-
Mariano Gomez, MIS, MCITP, PMP
IntellPartners, LLC.
http://www.intellpartners.com/
Blog http://dynamicsgpblogster.blogspot.com/