Excerpt |
---|
Using date-time fields in Load Replace filter settings – Source Field and Target Value or Constant. |
Key Points
DATEADD(DAY,-<n-days>,GETDATE())
resolves to the same time of day two days before.
Note |
---|
The T-SQL
|
...
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 | |||||
---|---|---|---|---|---|---|
Why not row # 2? | 1 | |||||
Why? | 1 and 2 |
Target Value or Constant Expression with and withoutCONVERT(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)
Excerpt | ||
---|---|---|
| ||
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 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 ColumnsWhen run on 2024-05-03 at 10:24:48.168 the expression When a Sage100 Sage 100 table (source type ODBC) is extracted as part of the source for a Load Replace, the step in That is, the comparison logic Workaround: The solution is use a date expression in Target Value or Constant that resolves to a date, rather than a day-time. |
Anchor | ||||
---|---|---|---|---|
|
Insert excerpt | ||||||||
---|---|---|---|---|---|---|---|---|
|
Related Pages
For Sage100 see also ETL+ for Sage 100 Providex especially Sage 100 Providex Dates.
...