Mapping date subtraction functions: DateDiffMapper element
DATEDIFF takes three arguments: a date part, a start timestamp, and an end timestamp. It returns the integer delta between the part of the two timestamps specified by the date part:
Integer datediff( datepart Varchar, start Timestamp, end Timestamp )
The DateDiffMapper element is used to customize the mappings for the date parts listed in Table 11-11. The table also shows the default template for each date part.
Examples: Mapping the DATEDIFF function with date part yyyy
The following examples show different ways of mapping the DATEDIFF function with date part yyyy.
Example 1
<FunctionMapping FunctionName="DATEDIFF"
  DatePart="yyyy">
  (YEAR( $P1 ) - YEAR ( $P0 ))
</FunctionMapping>
Example 2
<FunctionMapping FunctionName="DATEDIFF"
  DatePart="yyyy">
  CAST(
    TO_NUMBER( TO_CHAR( $P1, 'YYYY' ) )
    -
    TO_NUMBER( TO_CHAR( $P0, 'YYYY' ) )
    AS NUMBER(9)
    )
</FunctionMapping>
Example 3
<FunctionMapping FunctionName="DATEDIFF"
  DatePart="yyyy">
  DATEDIFF( year, $P0, $P1 )
</FunctionMapping>

Additional Links:

Copyright Actuate Corporation 2012