Arithmetic overflow error when converting expressi

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?

Answer 1

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)