Design

Back Next

dateDiff

Returns the length of time between two dates. You can specify the format as years, months, days, hours, minutes, seconds, milliseconds, or microseconds. The dateDiff function subtracts the second date from the first date and returns the difference.

The dateDiff function calculates the value based on the number of months instead of the number of days. It calculates the date differences for partial months with the days selected in each month. To calculate the date difference for the partial month, dateDiff adds the days used within the month. It then divides the value with the total number of days in the selected month.

The dateDiff function gives a different value for the same period in the leap year period and a non-leap year period. The difference occurs when February is part of the dateDiff function. The dateDiff function divides the days with 29 for February for a leap year and 28 if it is not a leap year.

For example, you want to calculate the number of months from September 13 to February 19. In a leap year period, dateDiff calculates the month of February as 19/29 months or 0.655 months. In a non-leap year period, dateDiff calculates the month of February as 19/28 months or 0.678 months. The dateDiff function similarly calculates the difference in the dates for the remaining months and the dateDiff function returns the total value for the specified period.

Some databases might use a different algorithm to calculate the difference in dates.

Syntax

date:dateDiff(xs:dateTime('date'), xs:dateTime('date'), 'format')

You must manually add the xs:dateTime phrase and enclose the date values within single quotation marks.

The following table describes the arguments:

Argument	Required/ Optional	Description
date	Required	Date/Time data type. Passes the values for the first date that you want to compare. You can enter any valid transformation expression.
date	Required	Date/Time data type. Passes the values for the second date that you want to compare. You can enter any valid transformation expression.
format	Required	Format string that specifies the date or time measurement. You can specify years, months, days, hours, minutes, seconds, milliseconds, or microseconds. You can specify only one part of the date, such as 'mm'. Enclose the format strings within single quotation marks. The format string is not case sensitive. For example, the format string 'mm' is the same as 'MM', 'Mm', or 'mM'.

Return Value

Double value. If the first date is later than the second date, the return value is a positive number. If the first date is earlier than the second date, the return value is a negative number.

0 if the dates are the same.

NULL if one (or both) of the date values is NULL.

Examples

The following expressions return the number of hours between the DATE_PROMISED and DATE_SHIPPED columns:

date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'HH')
date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'HH12')
date:dateDiff(xs:dateTime('DATE_PROMISED'), xs:dateTime('DATE_SHIPPED'), 'HH24')