Versions Compared

Old Version 6

changes.mady.by.user Cortlandt _

Saved on

compared with

New Version 7

changes.mady.by.user Cortlandt _

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Excerpt

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

Key Points

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

Note

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 … FROM Example_Table WHERE …

Rows #s returned

Code Block
languagesql
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

Code Block
languagesql
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 withoutCONVERT(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)

Excerpt
namedatetime

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

If IFthe date column selected for Source Field only carries date values (no timea 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()) expression 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 Sage100 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

When a Sage100 Sage 100 table (source type ODBC) is extracted as part of the source for a Load Replace, the step in
the Load Replace process will evaluate the WHERE logic 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 datetime columnvalue Target Value or Constant.

That is, the comparison logic WHERE 2024-0503-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.

Anchor
processsteps
processsteps
Load Replace Process – Step-by-Step

Insert excerpt
ETL+ Load Replace
ETL+ Load Replace
nameloadprocesssteps
nopaneltrue

Related Pages

...

Related Pages