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#

Leave a Reply

Your email address will not be published.