Arithmetic overflow error when converting expression to data type datetime

Question

In the following query:

SELECT 
  DU.MESSAGEINSERTDATE 
from [MYSMSREPORT].[DBO].[SIXDAYSHISTORY] 
where
  (CONVERT(NVARCHAR, CONVERT(DATETIME, MESSAGEINSERTDATE, 104), 112) 
  BETWEEN 
     CONVERT(NVARCHAR, CONVERT(DATETIME, 01/08/2008, 104), 112) 
  AND CONVERT(NVARCHAR,CONVERT(DATETIME,31/09/2012,104),112))

I have this error:

Arithmetic overflow error converting expression to data type datetime

What is the problem?

I edited your question to be more clear. Please review this edit. — Mahmoud Gamal
– Mahmoud Gamal, Commented Sep 26, 2012 at 10:02
did you try single quotes around the dates? '01/08/2008' and '31/09/2012' — Taryn
– Taryn, Commented Sep 26, 2012 at 10:04
In general, when you have more information to add, you should edit your existing question, not open a new one. I've voted to close the other one — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Sep 26, 2012 at 10:39

marc_s · Accepted Answer · 2012-09-26 11:37:05Z

2

The 31st September isn't a date, so SQL cannot convert it to one.

As an aside you should hard code dates in a culture inspecific format (yyyyMMdd). 01/08/2012 may convert to 1st August on your Server, but run on another and it could be 8th January. 20120801 will convert to 1st August on all machines.

In addition Why are you converting dates to NVARCHAR? You are removing any benfit of indices you have, and also performing needless implicit and explicit conversions. Assuming MESSAGEINSERTDATE is a DATETIME Column you could just use

WHERE MESSAGEINSERTDATE BETWEEN '20120801' AND '20120930'

If you need to remove the time from MESSAGEINSERTDATE use

CAST(MESSAGEINSERTDATE AS DATE)

edited Sep 26, 2012 at 11:37

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 26, 2012 at 10:16

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Damien_The_Unbeliever Over a year ago

Rather than removing the time (which again negates any index usage), better would be MESSAGEINSERTDATE >= '20120801' AND MESSAGEINSERTDATE < '20121001' (i.e. switch to a semi-open interval)

Martin Smith Over a year ago

@Damien_The_Unbeliever - CAST( AS DATE) is sargable

GarethD Over a year ago

Check the execution plan here. The index is used despite using CAST( AS DATE).

Collectives™ on Stack Overflow

Arithmetic overflow error when converting expression to data type datetime

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related