I have been getting the following error when running a SQL to convert my data type value from varchar
to datetime
.
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
I have checked the data and can't see anything to odd: Ran the following checks and all returning no results
SELECT [Date] from table where [DATe] is null
SELECT [Date] from table where [DATe] = ''
SELECT [Date] from table where LEN([date])> 10
SELECT [Date] from table where LEN([date])< 10
SELECT top 100 [Date] , SUBSTRING([date],4,2) from [table where convert(int, SUBSTRING([date],4,2)) < 1 or convert(int, SUBSTRING([date],4,2)) > 12
SELECT top 100 [Date] , SUBSTRING([date],1,2) from table where convert(int, SUBSTRING([date],4,2)) < 1 or convert(int, SUBSTRING([date],4,2)) > 31
Is there anything else worth looking at and maybe worth any pointers or help with this issue? Can't seem to get bottom of it.
Best Answer
I have faced the same problem a week ago. The problem is with the time zone setting. Specify in other formats like mm/dd/yyyy (usually works).
Specifying the date as 30/12/2013 resulted in the error for me. However, specifying it as mm/dd/yyyy format worked.
If you need to convert your input the you can try looking into the
CONVERT
method. Syntax isCONVERT(VARCHAR,@your_date_Value,103)
CONVERT(VARCHAR, '12/30/2013', 103)
The finishing 103 is the datetime format.
Refer this link for conversion formats and further reading. https://www.w3schools.com/sql/func_sqlserver_convert.asp