ETL+ Load Replace with Date-time fields

Owned by Cortlandt _
Last updated: Jun 02, 2024
3 min read

Using date-time fields in Load Replace filter settings – Source Field and Target Value or Constant.
Special caution for Sage 100 users.

GETDATE() – Key Points

Unless GETDATE() is executed exactly at midnight the datetime output from GETDATE() is not the same as a date, e.g.;; a date from CONVERT(DATE, 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.

  • GETDATE() returns the current system date-time ('YYYY-MM-DD hh:mm:ss.mmm').

  • DATEADD(DAY, <n-days>, GETDATE()) resolves to the same time of day two days before.

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.

image-20231213-020122.png
If run on 2024-03-07 at 10:24:48.168 
this expression would resolve to 2024-03-01 10:24:48.168

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.

Example_Table

Example_Table

Row

InvoiceDate

1

2023-01-01 00:00:00.000

2

2024-03-01 00:00:00.000

3

2024-03-02 00:00:00.000

SELECT … FROM Example_Table WHERE …

Rows #s returned

SELECT … FROM Example_Table WHERE …

Rows #s returned

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

Why not row # 2? 2024-03-01 00:00:00.000 is less than 2024-03-01 10:24:48.168

1

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

Why? 2024-03-01 should be converted internally to 2024-03-01 00:00:00.000

1 and 2

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

Example: When run on 2024-03-03 at 10:24:48.168 

Example: When run on 2024-03-03 at 10:24:48.168 

T-SQL Expression

DATEADD(DAY,-2,GETDATE())

DATEADD(DAY,-2,CONVERT(DATE, GETDATE())

returns value

2024-03-01 10:24:48.168

 2024-01-03

data type

datetime

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.

image-20240505-061911.png
Target Value or Constant value =
DATEADD(DAY,-2,CONVERT(DATE, GETDATE()) 

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