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

Posted on : 02-05-2012 | By : Devi Prasad | In : SQL Errors, T-SQL Code Examples

Share:

0


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:

SELECT CAST( '2012-04-31' AS DATETIME)

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:

SELECT CAST( '2012-04-30 24:00:00.000' AS DATETIME)
SELECT CAST( '2012-04-30 00:60:00.000' AS DATETIME)
SELECT CAST( '2012-04-30 00:00:60.000' AS DATETIME)
SELECT CAST( '2012-04-30 24:00:00.000' AS DATETIME)

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#

(Visited 572 times, 1 visits today)



References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Write a comment



Tags: , , , , , ,