How to right join a date table in T-SQL?

Question

I have done RIGHT JOINS in the past with no problems. However, for some reason now, I am not able successfully join a date table on the date field. In summary, I have two tables. The first table has one date column and a couple more non-date columns. Then I have a date table, which just has two date columns.

I initialize this date table by inserting into it first of month dates for 13 consecutive months. But my other table only has 9 months of data.

So table A looks like:

col_A    col_B     col_C
-----    ------    -------
sfds     jkjlj     7-1-2009
rewr     sfsfsd    5-1-2009
xcxvg    sdfsfk    4-1-2009
...

But table B looks like:

StartDate   EndDate
---------   ---------
7-1-2009    7-31-2009
6-1-2009    6-30-2009
5-1-2009    5-31-2009
...

But when I right join table B onto A like so:

    SELECT *
      FROM TABLE_A A
RIGHT JOIN TABLE_B B ON A.COL_C = B.StartDate

I expect to get 12 months of data since Table_B has 13 months/records. However, instead I am only getting 9 months total. Does anybody understand why this would be? And other things I might try to achieve the same result?

My main goal is to make Table_A include every month for past 13 months, even if there are null values. Right now though, it just includes 9 months since there no records for the other 4 months.

Answer 1

Joining on date columns can be problematic if there is a time portion to the dates (and there often is!).

It's safer to use DATEPART() or CONVERT() (or other date functions) to extract just the date portion but this will probably exclude using indexes.

one way is to use:

DATEADD(day, DATEDIFF(day, 0, DateColumn), 0)

i.e.

SELECT *      
FROM TABLE_A A
RIGHT JOIN TABLE_B B ON 
   DATEADD(day, DATEDIFF(day, 0, A.COL_C), 0) = DATEADD(day, DATEDIFF(day, 0, B.StartDate), 0)

There is also this form:

CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

but I prefer the former.

Answer 2

I just tried this and got all months, columns col_A, col_B and col_C being null where there wasn't a match. Are you sure all your dates are there and there isn't a where clause affecting this?

SELECT *
FROM TABLE_A A

sfds    jkjlj   2009-07-01 00:00:00.000


SELECT *
FROM TABLE_B B

2009-07-01 00:00:00.000 2009-07-31 00:00:00.000
2009-06-01 00:00:00.000 2009-06-30 00:00:00.000
2009-05-01 00:00:00.000 2009-05-31 00:00:00.000


SELECT *
FROM TABLE_A A
RIGHT JOIN TABLE_B B ON A.COL_C = B.StartDate

sfds    jkjlj   2009-07-01 00:00:00.000 2009-07-01 00:00:00.000 2009-07-31 00:00:00.000
NULL    NULL    NULL    2009-06-01 00:00:00.000 2009-06-30 00:00:00.000
NULL    NULL    NULL    2009-05-01 00:00:00.000 2009-05-31 00:00:00.000