Mapping date addition functions: DateAddMapper element

DATEADD takes three arguments: a date part, an integer delta value, and a timestamp value. It returns a timestamp that applies the delta value to the specified part of the original timestamp:

Timestamp dateadd( datepart Varchar, delta Integer, value Timestamp )

The DateAddMapper element is used to customize the mappings for the date parts listed in Table 11-12. The table also shows the default template for each date part.

Table 11-12

Default templates for mapping date parts with the DATEADD function

Date part	Default template
yyyy (year)	{FN TIMESTAMPADD (SQL_TSI_ MONTH, $P0*12, $P1)}
q (quarter)	{FN TIMESTAMPADD (SQL_TSI_ MONTH, $P0*3, $P1)}
m (month)	{FN TIMESTAMPADD (SQL_TSI_MONTH, $P0, $P1)}
d (day)	{FN TIMESTAMPADD (SQL_TSI_DAY, $P0, $P1)}
h (hour)	{FN TIMESTAMPADD (SQL_TSI_HOUR, $P0, $P1)}
n (minute)	{FN TIMESTAMPADD (SQL_TSI_MINUTE, $P0, $P1)}
s (second)	{FN TIMESTAMPADD (SQL_TSI_SECOND, $P0, $P1)}
w (day of week)	{FN TIMESTAMPADD (SQL_TSI_DAY, $P0, $P1)}
y (day of year)	{FN TIMESTAMPADD (SQL_TSI_DAY, $P0, $P1)}

Example: Mapping the DATEADD functions

Your database has a different syntax for the DATEADD functions. You define each part using a mapping:

<DateAddMapper>

  <FunctionMappings>

    <FunctionMapping FunctionName="DATEADD"

      DatePart="yyyy">

      ($P1 + $P0 YEARS)

    </FunctionMapping>

    …

    <FunctionMapping FunctionName="DATEADD"

      DatePart="y">

      ($P1 + $P0 DAYS)

    </FunctionMapping>

  </FunctionMappings>

</DateAddMapper>

Copyright Actuate Corporation 2012