Excerpt |
---|
Using date-time fields in Load Replace filter settings – Source Field and Target Value or Constant. Use of CONVERT(DATE, GETDATE()) instead of just GETDATE(). |
GETDATE() – Key Points
CONVERT(DATE, GETDATE()) Preferred Over GETDATE()
Example: When run on | ||
---|---|---|
T-SQL Expression | DATEADD(DAY,-2,GETDATE()) | DATEADD(DAY,-2,CONVERT(DATE, GETDATE()) |
returns value |
| |
data type | datetime | date |
Panel | ||||||||
---|---|---|---|---|---|---|---|---|
| ||||||||
Unless |
Extracting all the source records for an entire day, requires a) a filter that compares just the date ('YYYY-MM-DD') or b) 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
.
Panel | ||
---|---|---|
| ||
|
Note |
---|
The T-SQL
|
...
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.