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
did you try single quotes around the dates? '01/08/2008' and '31/09/2012' — Taryn♦
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

GarethD GarethD · Accepted Answer · 2012-09-26T10:16:54

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)

Arithmetic overflow error when converting expression to data type datetime

1 Answers