All we need is an easy explanation of the problem, so here it is.
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.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 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
Method 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!
Method 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 )
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0