SQL doesn't allow to cast date column to datetime?

Question

Select * 
from tableA 
inner join tableB on tableA.id = tableB.aid 
                  and cast(a.date AS DATETIME) = CAST('2015-08-24' AS DATETIME) 

Values that stored in tableA.date are '2015-08-24' meaning data has no issue.

When I execute the above statement, I get

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

May I know why cant cast a date column to datetime?

Answer 1

The root cause of the problem is this:

• the data type DATE has a range of accepted values from 01-01-0001 through 12-31-9999
• the data type DATETIME has a range of accepted values from 01-01-1753 through 12-31-9999

So if you happen to have a DATE from before 1753, or an empty / NULL value - this will be outside the range that DATETIME can handle.

You should stop using DATETIME In SQL Server 2008 and newer. Use DATETIME2(n) instead (where n stands for the number of fractional seconds you need).

So try this:

select * 
from tableA 
inner join tableB on tableA.id = tableB.aid 
                  and cast(a.date AS DATETIME2(3)) = CAST('2015-08-24' AS DATETIME2(3)) 

and I'm sure this'll work just fine.