When you try to convert varchar datatype to datetime you would get this error when the value in varchar datatype does not represent the correct range of date.
Example Queries:
[sql]
SELECT CAST( ‘2012-04-31’ AS DATETIME)
[/sql]
Result:
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.
Other Sample queries:
[sql]
SELECT CAST( ‘2012-04-30 24:00:00.000’ AS DATETIME)
[/sql]
[sql]
SELECT CAST( ‘2012-04-30 00:60:00.000’ AS DATETIME)
[/sql]
[sql]
SELECT CAST( ‘2012-04-30 00:00:60.000’ AS DATETIME)
[/sql]
[sql]
SELECT CAST( ‘2012-04-30 24:00:00.000’ AS DATETIME)
[/sql]
Fix:
– Check if the values in the varchar data type are in the range of datetime datatype. Use this link to check the same: Range of datetime data type
– Check if the values for month, day, hours, minutes, seconds, milliseconds are valid.
Applicable to: SQL Server 2005, SQL Server 2008,SQL Server 2008 r2,SQL Server 2012, ASP, C#