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

by

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 Date

After executing the above query, I encountered the error shown in the screenshot below.

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

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 conversion of a date data type to a datetime data type resulted in an out-of-range value sql

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.