Versions Compared

Old Version 7

changes.mady.by.user Cortlandt _

Saved on

compared with

New Version 8

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.

GETDATE() – Key Points

Extracting all the records for a full day, all the records for the entire day, requires a filter that compares just the date ('YYYY-MM-DD') or a date-time that starts at midnight (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.

Panel
bgColor#DEEBFF

  • 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.

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.

...