Join table by date only exclude seconds minutes

Question

I need to join on two tables based on date value.

TableA got the datetime with seconds, minutes included like 2001-04-30 16:59:00.000

TableB got the datetime without seconds, minutes included like 2001-04-30 00:00:00.000

I want to join by date only

select * from dbo.TableA  m
LEFT JOIN TableB z ON z.DocId=m.DocId
AND CHARGE_DATE=z.DocDate

not giving proper result

Answer 1

As long as you're 100% certain that TableB always has no time:

AND m.CHARGE_DATE >= z.DocDate
AND m.CHARGE_DATE <  DATEADD(DAY, 1, z.DocDate)

Or

AND CONVERT(DATE, m.CHARGE_DATE) = z.DocDate

You may want to flip these around depending on the usefulness of indexes on either of these columns.