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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.