1 Reply Latest reply on Jun 20, 2018 10:27 AM by Yuriy Fal

    Cross Database Joins w/ Date

    Lauren Schmidt

      Hello,

      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.

       

      Thanks!
      Lauren

        • 1. Re: Cross Database Joins w/ Date
          Yuriy Fal

          Hi Lauren,

           

          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] )

           

          Yours,

          Yuri