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.

Try incorporating ISDATE() just to make sure the data is valid. (It sounds like tableA.date is a string.)

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

Use the format yyyymmdd which is universal in SQL Server,

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

I did not see the problem with the '0001-01-01' value in a.Date. You could do a bit dirty trick like this:

Select * from tableA inner join tableB on tableA.id = tableB.aid and case when substring(a.date, 1, 2) not in ('19', '20') then null else CAST(a.date AS DATETIME) end = CAST('20150824' AS DATETIME)