Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions
  8. Creating Custom Functions
  9. Custom Function API Reference

Transformation Language Reference

Transformation Language Reference

DATE_DIFF

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
PowerCenter Integration Service
subtracts the second date from the first date and returns the difference.
The
PowerCenter 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
PowerCenter 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
PowerCenter 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
PowerCenter 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.
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