Arithmetic overflow error converting expression to data type datetime


ERROR: Arithmetic overflow error converting expression to data type datetime

This error occurs because of the Dateformat mismatch (Culture Problem)

Date : 26/02/2010

This date is in the format DMY(Day-Month-Year), But US servers will support
only the format of YMD (Year-Month-Day). So by this time you will get the
afore- mentioned error.

Let me explain with a SAMPLE;


DECLARE @Date NVARCHAR(50)
SET @Date = '26-02-2010 00:00:000'
SELECT CONVERT(DATETIME, @Date)


If you execute the above 3 Sql Statements, you will get an error like

"Arithmetic overflow error converting expression to data type datetime"

suppose, if you store the @Date in a VARCHAR Datatype;


DECLARE @Date VARCHAR(50)
SET @Date = '26-02-2010 00:00:000'
SELECT CONVERT(DATETIME, @Date)


you will get an error like this

"The conversion of a char data type resulted in an out-of-range datetime value."

SOLUTION:
---------

To overcome these type of errors, use SET DATEFORMAT;


SET DATEFORMAT DMY
DECLARE @Date NVARCHAR(50)
SET @Date = '26-02-2010 00:00:000'
SELECT CONVERT(DATETIME, @Date)


Now the error will be resolved, instead you would get the desired result as
output :-)


Comments



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: