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

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.