The tables are proprietary from one of those wealth management companies, so can't post their actual stuff, but will "simulate":
Left table: TDate Price Account OtherStuff -----LEFT JOIN-----> Right Table: HDate HName HDay
12/25/2018 24.12 12345 xyzxyz 12/25/2018 Christmas Tuesday
Following queries ALL FAIL!
SELECT TDate, Price, HDate, HName from LeftTable Left Join RightTable on TDate = HDate; (Yields nothing whatsoever, not even from LeftTable )
SELECT TDate, Price, HDate, HName from LeftTable Left Join RightTable on TDate = HDate WHERE TDate = #12/23/2018#; (Yields nothing, should show Dec. 23rd stuff from LeftTable and nulls for stuff from RightTable since Dec. 23rd does not exist there)
SELECT TDate, Price, HDate, HName from LeftTable Left Join RightTable on TDate = HDate WHERE HDate Is Not Null; (Yields nothing, should show Dec. 25th stuff from RightTable)
SELECT TDate, Price, HDate, HName from LeftTable Left Join RightTable on TDate = HDate WHERE HDate = Is Null; (Yields nothing, should show Dec 25th stuff from LeftTable and nulls fro RightTable)
EDITING, a half hour later … Creating same tables in a totally new database, joins and queries worked exactly as expected, so apparently there's something odd about the customer's database, a setting or whatever that's hosing up the works. Any thoughts what that might be? If not, I guess I'll just re-create both of the tables in question within the customer's database and replace the originals to see what happens. Fortunately I can do that in a working copy, so if it goes badly, no harm no foul.
So to give myself a little credit, I kinda do know how fairly simple select queries ought to work, and it's baffling me why the date fields in these two tables don't want to join. Other fields are NOT relevant, as even a query just for the two date fields fails.
I'm going to re-create these two tables to a point in a totally local database on my home PC, maybe just for a month or so of data, and see what happens, but in the meantime any further aha's from y'all will certainly be appreciated and considered.