DATE_DIFF
Returns the length of time between two dates. You can request the format to be years, months, days, hours, minutes, seconds, milliseconds, microseconds, or nanoseconds. The Data Integration Service subtracts the second date from the first date and returns the difference.
The Data Integration Service calculates the DATE_DIFF function 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, the Data Integration Service adds the days used within the month. It then divides the value with the total number of days in the selected month.
The Data Integration Service 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 DATE_DIFF function. The DATE_DIFF 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, the DATE_DIFF function calculates the month of February as 19/29 months or 0.655 months. In a non-leap year period, the DATE_DIFF function calculates the month of February as 19/28 months or 0.678 months. The Data Integration Service similarly calculates the difference in the dates for the remaining months and the DATE_DIFF function returns the totaled value for the specified period.
Note: Some databases might use a different algorithm to calculate the difference in dates.
Syntax
DATE_DIFF( date1, date2, format )
The following table describes the arguments for this command:
Argument | Required/ Optional | Description |
|---|
date1 | Required | Date/Time datatype. Passes the values for the first date you want to compare. You can enter any valid transformation expression. |
date2 | Required | Date/Time datatype. Passes the values for the second date you want to compare. You can enter any valid transformation expression. |
format | Required | Format string specifying the date or time measurement. You can specify years, months, days, hours, minutes, seconds, milliseconds, microseconds, or nanoseconds. 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 date1 is later than date2, the return value is a positive number. If date1 is earlier than date2, 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 ports:
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'HH' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'HH12' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'HH24' )
DATE_PROMISED | DATE_SHIPPED | RETURN VALUE |
|---|
Jan 1 1997 12:00:00AM | Mar 29 1997 12:00:00PM | -2100 |
Mar 29 1997 12:00:00PM | Jan 1 1997 12:00:00AM | 2100 |
NULL | Dec 10 1997 5:55:10PM | NULL |
Dec 10 1997 5:55:10PM | NULL | NULL |
Jun 3 1997 1:13:46PM | Aug 23 1996 4:20:16PM | 6812.89166666667 |
Feb 19 2004 12:00:00PM | Feb 19 2005 12:00:00PM | -8784 |
The following expressions return the number of days between the DATE_PROMISED and the DATE_SHIPPED ports:
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'D' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'DD' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'DDD' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'DY' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'DAY' )
DATE_PROMISED | DATE_SHIPPED | RETURN VALUE |
|---|
Jan 1 1997 12:00:00AM | Mar 29 1997 12:00:00PM | -87.5 |
Mar 29 1997 12:00:00PM | Jan 1 1997 12:00:00AM | 87.5 |
NULL | Dec 10 1997 5:55:10PM | NULL |
Dec 10 1997 5:55:10PM | NULL | NULL |
Jun 3 1997 1:13:46PM | Aug 23 1996 4:20:16PM | 283.870486111111 |
Feb 19 2004 12:00:00PM | Feb 19 2005 12:00:00PM | -366 |
The following expressions return the number of months between the DATE_PROMISED and DATE_SHIPPED ports:
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'MM' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'MON' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'MONTH' )
DATE_PROMISED | DATE_SHIPPED | RETURN VALUE |
|---|
Jan 1 1997 12:00:00AM | Mar 29 1997 12:00:00PM | -2.91935483870968 |
Mar 29 1997 12:00:00PM | Jan 1 1997 12:00:00AM | 2.91935483870968 |
NULL | Dec 10 1997 5:55:10PM | NULL |
Dec 10 1997 5:55:10PM | NULL | NULL |
Jun 3 1997 1:13:46PM | Aug 23 1996 4:20:16PM | 9.3290162037037 |
Feb 19 2004 12:00:00PM | Feb 19 2005 12:00:00PM | -12 |
The following expressions return the number of years between the DATE_PROMISED and DATE_SHIPPED ports:
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'Y' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'YY' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'YYY' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'YYYY' )
DATE_PROMISED | DATE_SHIPPED | RETURN VALUE |
|---|
Jan 1 1997 12:00:00AM | Mar 29 1997 12:00:00PM | -0.24327956989247 |
Mar 29 1997 12:00:00PM | Jan 1 1997 12:00:00AM | 0.24327956989247 |
NULL | Dec 10 1997 5:55:10PM | NULL |
Dec 10 1997 5:55:10PM | NULL | NULL |
Jun 3 1997 1:13:46PM | Aug 23 1996 4:20:16PM | 0.77741801697531 |
Feb 19 2004 12:00:00PM | Feb 19 2005 12:00:00PM | -1 |
The following expressions return the number of months between the DATE_PROMISED and DATE_SHIPPED ports:
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'MM' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'MON' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'MONTH' )
DATE_PROMISED | DATE_SHIPPED | LEAP YEAR VALUE
(in Months) | NON-LEAP YEAR VALUE
(in Months) |
|---|
Sept 13 | Feb 19 | -5.237931034 | -5.260714286 |
NULL | Feb 19 | NULL | N/A |
Sept 13 | NULL | NULL | N/A |