Special caution for Sage 100 users.
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
Example: When run on | ||
|---|---|---|
T-SQL Expression | DATEADD(DAY,-2,GETDATE()) | DATEADD(DAY,-2,CONVERT(DATE, GETDATE()) |
returns value |
| |
data type | datetime | date |
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 (no time, or time = 00:00:00.000) then use a 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 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. 2024-05-01 00:00:00.000.
When a Sage100 table (source type ODBC) is extracted as part of a Load Replace, the step in the Load Replace process will evaluate the WHERE logic as expected but Sage100 will ignore the time portion of the datetime column.
WHERE 2024-05-01 00:00:00.000 >= 2024-05-03 10:24:48.168
For Sage100 see also ETL+ for Sage 100 Providex especially Sage 100 Providex Dates.