ETL+ Load Replace with Date-time fields

Using date-time fields in Load Replace filter settings – Source Field and Target Value or Constant. Use of CONVERT(DATE, GETDATE()) instead of just GETDATE().
Special caution for Sage 100 users.

GETDATE() – Key Points

CONVERT(DATE, GETDATE()) Preferred Over GETDATE()

Extracting all the source records for an entire day, requires a) a filter that compares just the date ('YYYY-MM-DD') or b) a date-time that starts at midnight ('YYYY-MM-DD 00:00:00.000').
First Issue: Extracting and loading records from a partial day may lead to unexpected results on reports.

Second Issue: Data from Sage 100 ODBC (Providex) sources may only filter and extract as expected when filtered by a date or a time-time value with time = 00:00:00.000. For example 2024-03-01 00:00:00.000.

The T-SQL DATEADD(DAY,-<n-days>,GETDATE()) resolve to the same time of day two days before.

1. For instance, a expression in Target Value or Constant that returns 2024-03-01 10:24:48.168 won't replace rows with datetime values for the first 10 hours, 24 minutes of 2024-03-01.

2. Source date-time fields from Sage 100 ODBC/Providex source systems don’t evaluate date-time comparisons the same way MS SQL Server does. This can result in missing or incorrect delta loads.

Source Field

Often the columns named in Source Field are date-time fields where the time part of the datetime data type = 00:00:00.000.

SELECT InvoiceDate FROM Example_Table WHERE InvoiceDate >= 2024-01-03 10:24:48.168

SELECT InvoiceDate FROM Example_Table WHERE InvoiceDate >= 2024-01-03

Target Value or Constant Expression with and without CONVERT(DATE ...

Recommended Practice:
When DateTime Fields Only Have Dates (Time part = 00:00:00.000)

When Source Fields with DateTime data types contain only dates. (Time is always = 00:00:00.000)

IF the date column selected for Source Field only carries date values (a field with date data type, or a datetime field with the time part = 00:00:00.000)
THEN then use a date expression that references GETDATE() with CONVERT(DATE, GETDATE()) instead of just GETDATE() in Target Value or Constant.

Load Replace is a several step process involving queries against the target table in the data warehouse and a extract from the data source. These steps reply on different file or data base processing systems. This difference in software, variations in SQL dialects, etc. can pose challenges for maintaining logical consistency across processing systems.

Know Issues with Sage 100 Date & DateTime Columns

When run on 2024-05-03 at 10:24:48.168  the expression DATEADD(DAY,-2,CONVERT(DATE, GETDATE())    results in a date value of 2024-05-01. When the date field is compared  2024-05-01 00:00:00.000.

When a Sage 100 table (source type ODBC) is the source for a Load Replace,
the Load Replace process will evaluate the filter for the 3rd, Delete Selected Rows in Target step as expected but in the 4th, Extract from Source, step Sage100 will ignore the time portion of the value Target Value or Constant.

That is, the comparison logic WHERE 2024-03-01 00:00:00.000 >= 2024-05-03 10:24:48.168 is evaluated differently by MS SQL Server and Sage 100 resulting in inconsistencies and missing data.

Workaround: The solution is use a date expression in Target Value or Constant that resolves to a date, rather than a day-time.

Load Replace Process – Step-by-Step

1. Evaluate Target Value or Constant. Evaluate the T-SQL expression in Target Value or Constant clause.
   The expression evaluations to a single value. Save this value for the following steps.

2. First time? IF the Target Value of Constant expression from the step above resolves to NULL
   THEN run Load All and end the load.
   Otherwise continue with the next step.

3. Delete Selected Rows in Target. Delete all rows in the target table in the data warehouse where Source Field >= Target Value or Constant.

4. Extract from Source. Extract all rows/records from the source table where Source Field >= Target Value or Constant into a temporary staging table.

5. Load from Staging Table. Load (append) all rows in the staging table into the target data warehouse table.

Related Pages

• ETL+ Load Replace

• Microsoft T-SQL

  • T-SQL GETDATE() Function

• The GETDATE() function returns the current database system date and time, in a 'YYYY-MM-DD hh:mm:ss.mmm' format.

• For Sage100 see also ETL+ for Sage 100 Providex especially Sage 100 Providex Dates.