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

1. Within SQL Server we can store DATETIME values between 01/01/1753 and 12/31/9999 only. If we try to enter a value which isn't this range then it would throw an error.


3. The way you handle dates will depend on the DATEFORMAT set for your login. Internally DATEFORMAT takes its value from SET LANGUAGE.


/*
This code block would throw an error
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
*/
SET DATEFORMAT MDY
GO


DECLARE @dt VARCHAR(20)


--Format is Month Day Year. But we are passing 13 as month so it is expected to throw the error
SET @dt = '13/19/11 12:33:12'


SELECT CONVERT(DATETIME, @DT)
GO


--Now it will work!
SET DATEFORMAT DMY
GO


Declare @dt VARCHAR(20)
SET @dt =   '13/19/11 12:33:12' 


SELECT CONVERT(DATETIME, @DT)