SQL Join Two Tables With Two Common Columns - Datetime Issue

Question

I have two tables in SQL that I am trying to join.

Table1:

Date                             |        Name       |        Shoe_Size     |     Pay_method
2020-04-04 05:27:00.0000000      |    J.Bloggs       |         8            |      Credit
2020-04-04 05:55:00.0000000      |    J.Smith        |         10           |      Cash
2018-06-20 05:27:00.0000000      |    J.Bloggs       |         6            |      Cash
2018-08-15 05:55:00.0000000      |    J.Smith        |         9            |      Cash

Table2:

Date_ID                             |        Name       |        TShirt_Size 
2020-04-04 00:00:00.0000000         |    J.Bloggs       |         M
2020-04-04 00:00:00.0000000         |    J.Smith        |         L
2018-06-20 00:00:00.0000000         |    J.Bloggs       |         S
2018-08-15 00:00:00.0000000         |    J.Smith        |         M

I would like to combine the tables to show:

 Date                            |        Name       |       Shoe_Size    |  TShirt_Size
2020-04-04 05:27:00.0000000      |    J.Bloggs       |         8          |       M
2020-04-04 05:55:00.0000000      |    J.Smith        |         10         |       L
2018-06-20 05:27:00.0000000      |    J.Bloggs       |         6          |       S
2018-08-15 05:55:00.0000000      |    J.Smith        |         9          |       M

Code I tried:

SELECT a.Date, a.Name, a.Shoe_Size, b.DATE_ID, b.Name, b.Tshirt_Size
From Table1 a, Table2.b
WHERE a.Name = b.Name

But obviously doesn't account for the date dimension.

Things I'm not sure how to get:

1. How to join based off two common columns (name & Date).

2. The date in Table1 has the exact time while the date in Table2 just has the right day. There will only be one entry per name per day.

Any help would be much appreciated.

Answer 1

Try this - use proper ANSI JOINs, and compare on the date alone (no time portion):

SELECT 
    a.Date, a.Name, a.Shoe_Size, b.Name, b.Tshirt_Size
FROM 
    Table1 a
INNER JOIN 
    Table2 b ON a.Name = b.Name
             AND CAST(a.Date AS DATE) = CAST(b.Date_ID AS DATE)

Answer 2

Try this:

SELECT a.Date, a.Name, a.Shoe_Size, b.DATE_ID, b.Name, b.Tshirt_Size
From Table1 a
INNER JOIN Table2.b
    ON a.Name = b.Nam
    AND CONVERT(VARCHAR(10),a.Date, 121) = CONVERT(VARCHAR(10), b.date, 121)

Answer 3

I would do:

select
    t1.date,
    t1.name,
    t1.shoe_size,
    t2.tshirt_size
from table1 t1
inner join table2 t2 
    on  t1.name = t2.Name
    and t1.date >= t2.date
    and t1.date <  dateadd(day, 1, t2.date)

The upside of this approach is that no date function is applied on dates coming from table1 - I would expect that this has better performance than other solutions involving date functions on this column.