The conversion of a date data type to a DateTime data type resulted in an out-of-range value SQL

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

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
The conversion of a char data type to a DateTime data type resulted in an out-of-range DateTime value
The conversion of a date data type to a DateTime data type resulted in an out-of-range value SQL

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 conversion of a date data type to a datetime data type resulted in an out-of-range value sql
A valid use case in SQL Server

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.