Automatically incrementing date during load.

[Gethyn]

Hi,

I have a table loading from a SQL server and I need to load the data each day but restrict the data so that it only loads up to the end of the current day defined by one of the date fields in the table.

At the moment I have to manually change the date every day before the load, meaning that I can't use publisher to reload the data. Does anyone know a way that I can pick up the the end of current day or start of next day so that I don't have to hard code a date?

DutyAssignment:
LOAD "DUTY_ID",
 "OU_ID",
 applymap('CostCentreMap',"OU_ID") as [Cost Centre],
 date([PLANNED_DATE_TIME_START]) as [Shift Start],
 "ID_NUMBER" as Employee_ID,
 "SHT_ID",
 "DUTY_CANCEL_REASON_ID",
 "DUTY_CHANGE_REASON_ID",
 "PLANNED_DATE_TIME_START",
 Year(PLANNED_DATE_TIME_START) as Year,
 Month(PLANNED_DATE_TIME_START) as Month,
 Day(PLANNED_DATE_TIME_START) as Day,
 "PLANNED_DATE_TIME_END",
 "ESTIMATED_MINUTES",
 "TEMP_STAFF_REQ_REASON_ID",
 "RST_LOCATION_ID";
SQL SELECT *
FROM MAPSMSTR1.dbo."RST_DUTY_ASSIGNMENT"
Where VALID_FROM_DATE <= '2010-10-28 00:00:00.000';

Thanks,

Gethyn.

[sparur · Accepted solution]

hello

I think you should use today() function with variable:

LAET vToday = today();

SQL SELECT * FROM ....

WHERE VALID_FROM_DATE <= '$(vToday)';

[Gethyn]

Hi Anatoly,

That worked well, I just had to modify it slightly to format the date the same as the field.

LET vToday = date(today(), 'YYYY-MM-DD');

Thanks,

Gethyn.

[sparur]

I glad to help