Custom SQL Query: LEFT OUTER JOIN, not joining properly? | JMP 15

[#1]

Hello,

I'm trying to join WeeklyDOSummary (yellow) and PondMaster_Investigations (green). I've highlighted the columns selected on my custom SQL query.

From WeeklyDOSummary, I am left outer joining PondMaster_Investigations on date = date and pond = pond. Although you can't see it clearly in the photos, there are data in the Ave and NO23 columns where there are common dates (ranging from 05/07/2020 to 10/08/2020) and common Pond values (ranging from 15 to 20).

But when I run my custom SQL query, JMP only recognizes the entry from Pond 15, on 08/06/2020.

The dates in the Weekly DO Summary Table were entered at 1:00:00 AM, which is why I subtracted 1/24 from the Date column. 

The numerical value of the dates were equal between the two data tables when I checked them on Excel.

Any suggestions on how to work around this issue and join the two tables completely?

Thank you in advance!!

[#2 · Accepted solution]

The first thing I would do in JMP is to change the format of the date cols to continuous, numeric, fixed decimal, 1.

Just verify that the dates are indeed identical to the second and that excel didn't feed you something goofy.

I'd bet a nickel (collectable at the next JMP Discovery summit) that the dates aren't matching.

I might also make a new date index column that has lower precision, something like shortdate(:Date) in a formula column. That way the hour fraction of your date column doesn't matter.  

I hope its something simple like this (after all, I've got a nickel riding on it.)

P.S.  lol, "when I checked them in Excel"

[#3]

@Byron_JMP Thank you so much (take my nickel!)!! The dates weren't matching at all when I changed the WeeklyDOSummary values to fixed decimal. Once I created the shortdate formula column, the values aligned and I can finally move on with my analysis!

I've primarily been using Excel in the past so I was struggling with this issue on JMP forever (although now I see the solution was very very simple). I still don't understand why the dates weren't matching even though I accounted for the hour difference (1/24) on the SQL query and the dates seemed to be matching as numerical values on Excel (I thought the date values in Excel also accounts for seconds, but as decimals...). Do you know why that might be??

[#4]

In general, dates are frustrating, and there is so much variation in how they can be numerically expressed. 

my approach is to try to get to the actual number behind the formatted date value, or just take the text and use JMP's InFormat() function to parse the text to a number. 

There are so many things that can go wrong, that when it all works it feels like magic.