In this SQL Server tutorial, I will share my experience and the possible reasons behind the error “the conversion of a date data type to a DateTime data type resulted in an out-of-range value SQL“.
Recently, I was working with date and time data types in SQL Server. I was trying to check what date and time values are valid in SQL Server when I faced the error “the conversion of a date data type to a DateTime data type resulted in an out-of-range value SQL“.
The SQL query that I executed was:
SELECT CAST('02/29/2021' AS DATETIME) AS Date
Solution: The conversion of a date data type to a datetime data type resulted in an out-of-range value sql
We face this error if we supply a date value that is not valid. For example, If I enter the date 2021-12-32 i.e. 32 cannot be a day of any month, I will get the same error.
In my case, I entered the day value as 29 for the February month of a non-leap year. Then I entered a valid date i.e. 29 February and I got the expected result. The query became:
SELECT CAST('02/28/2021' AS DATETIME) AS [United States Date]
The following are common mistakes that we make while entering a valid date:
- If you specify any year before than 1753, you will face this error.
- Entering the day as 29 Februrary when the year is not a leap year.
- Entering the day as 31 April i.e. the April month has only 30 days.
- Entering the month value greater than 12 i.e. there are only 12 months in a year.
Therefore, while trying to specify a date value in SQL Server, keep in mind to specify the valid date and not to make the above mistakes.
You may also like to read the following SQL Server tutorials.
- SQL Server Convert Function + Examples
- SQL Server Convert String to Date + Examples
- SQL Server Trigger on Delete Insert Into Another Table
- SQL Server Convert Datetime to date + Examples
- SQL Server convert integer to string + 12 Examples
- SQL Server Convert Datetime to String + Examples
- Error: 40 – could not open a connection to sql server
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.