I have an excel workbook with two fields, date and weekday. I would like to do a cross database join on a table from a SQL Server Database. I cannot for the life of my figure out why I am not able to join my date field on my Excel file to a Date field in the SQL Server table.
I want to do a LEFT join from the SalesOrderHistoryHeader.DateCreated to BusinessDays.Date.
My ultimate goal for all this is to query sales for the prior business day excluding holidays. I cannot easily do this with the date field in the SQL table, so I have created my own table with a list of eligible dates.
I have attached the workbook and the excel file.
Any help would be appreciated.
It well may be because of the different Data Type of your Join columns.
The [Date] one from Excel is of the Date Type (sic),
but the [Date Created] one from MS SQL Server is of the Datetime Type.
As a workaround you may want to apply a Join Calculation on both, like this:
DATETRUNC( 'day', [Date] ) ON DATETRUNC( 'day', [Date Created] )