Long Date Not Recognizing Related Table Date

[Machi6108]

Long story short I have a table of data that includes a column with a date. I want to convert to fiscal dates, so created a table that has those dates, qurters, months correctly. Have a one to manny relathinship. 

However when I link them and use [realted, lookup, etc.] commands it comes up blanks. 

I'm guessing it is becuase the fiscal table stores dates as (11/23/2022 12:00:00 AM)

And my table has dates as (11/23/2022 5:35:21 AM) -> ie. more spcific with the time.

I've tried to convert to short date but that is more of a visual, so the basically the data table is not able to function as a primary key. 

Any tips? 

[Machi6108]

This worked thank you! 

[Greg_Deckler · Accepted solution]

@Machi6108 You could create a calculated column in your table like:

Date Midnight = DATE(YEAR([Date]),MONTH([Date]),DAY([Date]))