Incremental Date Function

$IncDate has two forms. In the first the function has following parameters:

  1. (optional) Date and optional time format. See date and time formats for details. The default is DD.MM.YYYY
  2. (optional) Initial value*, default is 01.01.1970 (and 00:00:00 if time present).
  3. (optional) Step*. Integer, default is 1. Negative values are acceptable.
  4. (optional) Step size. 'Y', 'M', 'D', 'H', 'm' and 'S' for year, month, day, hour, minute and second. The default is 'D'.
  5. (optional) Sequence border**, the function backs to the first value if the current value greater than this parameter. Date. Empty ("No cycle") is default.
  6. (optional) Reuse counter, integer, default is 1. The generator will return same value a few times if this parameter is positive.

In the second form "Initial value" parameter should be replaced by table name and column name. In this case, the engine will start incrementing after the latest available value of the column.

Important: use the $IncTime function instead for time increment only. It works faster.

* - the function call (like $Pattern) is acceptable for 'Initial value' and 'Step' instead of constant. Performance warning: it can work a few times slowly for some cases.

** - applicable to positive steps only.

Examples

  • $IncDate() - same as $IncDate(DD.MM.YYYY,01.01.1970,1,D). Output: 01.01.1970,02.01.1970,03.01.1970,04.01.1970,...
  • $IncDate(DDMMYY,150412,10,M) - start from 15/04/12, step is 10 months. Output: 150412, 150213,151213,151014,150815,...
  • $IncDate(DD/MM/YYYY,10/01/2000,-1,M) - decrement from 10/01/2000 by one month. Output: 10/01/2000,10/12/1999,10/11/1999,10/10/1999,...
  • $IncDate(DDMMYYYY,01012000,1,D,,3) - reuse each date 3 times. Output: 01012000,01012000,01012000, 02012000,02012000,02012000, 03012000,03012000...
  • $IncDate(DDMMYYYY,01012000,1,D,06012000) - sequence border is 06012000. Output: 01012000,02012000, 03012000,04012000, 05012000,06012000, 01012000,02012000,...
  • $IncDate(YYYY-MM-DD HH:mm:SS,,,M) - date with time format, increment by one month starting 1970-01-01. Output: 1970-01-01 00:00:00,1970-02-01 00:00:00,1970-03-01 00:00:00,...
  • $IncDate(,,$Vars(#DateStep)) - start from 01.01.1970 and use #DateStep variable as step value.
  • $IncDate(DD-MON-YYYY,$Pattern(@1),1,M) - use column #1 as initial value, step is 1 month.
  • li>$IncDate(DD-MON-YYYY,Orders,OrderDate,2,M) - the engine will find maximum value of 'OrderDate' column in 'Orders' table, adds 2 months and use results as initial value