Skip to main content
Skip table of contents

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

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())
recommended

returns value

2024-03-01 10:24:48.168

 2024-01-03

data type

datetime

date

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

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

SQL
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

SQL
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 

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

  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.

NOTES

  • Evaluate Target Value or Constant. The SQL expression in the Target Value or Constant box must be a T-SQL expression that can be evaluated and resolved into a single, constant value by MS SQL Server

Related Pages

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.