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 10‑11. The table also shows the default template for each date part.

Table 10‑11 Default templates for mapping date parts with the DATEDIFF function
Date part	Default template
yyyy (year)	{FN TIMESTAMPDIFF (SQL_TSI_YEAR, $P0, $P1)}
q (quarter)	{FN TIMESTAMPDIFF (SQL_TSI_QUARTER, $P0, $P1)}
m (month)	{FN TIMESTAMPDIFF (SQL_TSI_MONTH, $P0, $P1)}
d (day)	{FN TIMESTAMPDIFF (SQL_TSI_DAY, $P0, $P1)}
h (hour)	{FN TIMESTAMPDIFF (SQL_TSI_HOUR, $P0, $P1)}
n (minute)	{FN TIMESTAMPDIFF (SQL_TSI_MINUTE, $P0, $P1)}
s (second)	{FN TIMESTAMPDIFF (SQL_TSI_SECOND, $P0, $P1)}
w (day of week)	({FN TIMESTAMPDIFF (SQL_TSI_DAY, $P0, $P1)} / 7)
y (day of year)	{FN TIMESTAMPDIFF (SQL_TSI_DAY, $P0, $P1)}

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>