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”.
The conversion of a date data type to a DateTime data type resulted in an out-of-range value.
Recently, I was working with date and time data types in SQL Server. I was trying to check which date and time values are valid in SQL Server when I encountered this error.
The SQL query that I executed was:
SELECT CAST('02/29/2021' AS DATETIME) AS DateAfter executing the above query, I encountered the error shown in the screenshot below.
Solution
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 February in 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 1753, you will face this error.
- Entering the day as 29 February when the year is not a leap year.
- Entering the day as 31 April, i.e., the April month has only 30 days.
- Entering a month value greater than 12, i.e., there are only 12 months in a year.
Therefore, when specifying a date value in SQL Server, remember to use a valid date and avoid the above mistakes.
You may also find the following SQL Server tutorials helpful.
- 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.
