The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

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#