Arithmetic Overflow Error When Converting Expression To Data Type Datetime

In the following query: SELECT DU.MESSAGEINSERTDATE from [MYSMSREPORT].[DBO].[SIXDAYSHISTORY] where (CONVERT(NVARCHAR, CONVERT(DATETIME, MESSAGEINSERTDATE, 104), 112) BET

Solution 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)