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 filter that compares just the date ('YYYY-MM-DD') or 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.
For instance, a expression in Target Value or Constant that returns
2024-03-01 10:24:48.168won't replace rows with datetime values for the first 10 hours, 24 minutes of2024-03-01.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.
Example_Table | |
|---|---|
Row | InvoiceDate |
1 |
|
2 |
|
3 |
|
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? | 1 |
SELECT InvoiceDate FROM Example_Table WHERE InvoiceDate >= 2024-01-03 Why? | 1 and 2 |
Target Value or Constant Expression with and without CONVERT(DATE ...
Example: When run on | ||
|---|---|---|
T-SQL Expression | DATEADD(DAY,-2,GETDATE()) | DATEADD(DAY,-2,CONVERT(DATE, GETDATE()) |
returns value |
| |
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.
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
Evaluate Target Value or Constant. Evaluate the T-SQL expression in Target Value or Constant clause. First time? IF the Target Value of Constant expression from the step above resolves to NULL Delete Selected Rows in Target. Delete all rows in the target table in the data warehouse where Source Field >= Target Value or Constant. Extract from Source. Extract all rows/records from the source table where Source Field >= Target Value or Constant into a temporary staging table. Load from Staging Table. Load (append) all rows in the staging table into the target data warehouse table. 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
The expression evaluations to a single value. Save this value for the following steps.
THEN run Load All and end the load.
Otherwise continue with the next step.NOTES
Related Pages
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.