SQL left join returns null column [closed]

Question

I am trying to do a LEFT JOIN on two tables, where one table has a datetime column, and the other table has a date column. As I am working in SQL Server, I used CONVERT to change the datetime to date. I then wrote the join statement:

SELECT CONVERT(date,db1.dbo.table1.datecolumn), db2.dbo.table2.datecolumn
FROM db1.dbo.table1
LEFT JOIN db2.dbo.table2 
    ON db1.dbo.table1.datecolumn = db2.dbo.table2.datecolumn

And the query returns the date column from table1, but the column from table2 is populated with NULL values only. I am not sure what I am doing wrong. I tried CAST instead of CONVERT, with no luck. Is there something happening with the conversion? Any help is appreciated.

Answer 1

You're doing the convert() in the wrong place I guess. You have to do it in the equals operation of the ON, it's still a datetime there, regardless of what you do on it in the list of selected columns.

Try:

SELECT CONVERT(date,db1.dbo.table1.datecolumn), db2.dbo.table2.datecolumn
FROM db1.dbo.table1
LEFT JOIN db2.dbo.table2 
    ON CONVERT(date,db1.dbo.table1.datecolumn) = db2.dbo.table2.datecolumn

Answer 2

Actually there's not problem with the CONVERT function. The reason that why date's column from table2 is returning a NULL, it's because isn't the preserved side on the JOIN.

You're performing a LEFT JOIN between db1.dbo.table1 and db2.dbo.table2 tables, where the preserved side is db1.dbo.table1. Preserved side means that in case the matching condition specified at ON clause is evaluated either to 'false' or 'unknown', then column's values from the either left or right table (depends on which JOIN you're performing: LEFT or RIGHT) will be returned, whereas the column's values from the other side will be missing (a NULL will be used as placeholder).

Please, take a look at the following picture that shows how JOIN operations are represented graphically.

Image from here.

I hope this helps you!

Answer 3

ON db1.dbo.table1.datecolumn = db2.dbo.table2.datecolumn

because you say datecolumn is of type datetime , this is really hard to work. datetime is "YYYY-MM-DD hh:mm:ss[.nnn]" and to have two equal you need to have them equal by millisecond . More exactly by 3 milliseconds distance.

I think you need to convert both here

ON CONVERT(date,db1.dbo.table1.datecolumn) = CONVERT(date, db2.dbo.table2.datecolumn )