Versions Compared

Old Version 9

changes.mady.by.user Cortlandt _

Saved on

compared with

New Version Current

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. Use of CONVERT(DATE, GETDATE()) instead of just GETDATE().
Special caution for Sage 100 users.

GETDATE() – Key Points

...

CONVERT(DATE, GETDATE()) Preferred Over GETDATE()

Example: When run on 2024-03-03 at 10:24:48.168 

T-SQL Expression

DATEADD(DAY,-2,GETDATE())

DATEADD(DAY,-2,CONVERT(DATE, GETDATE())
recommended

returns value

2024-03-01 10:24:48.168

 2024-01-03

data type

datetime

date

Panel
panelIconId2600
panelIcon:sunny:
panelIconText☀️
bgColor#DEEBFF

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) 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
bgColor#DEEBFF#FFFFFF

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

...