Crystal Reports for Eclipse Designer Guide

DateDiff

Description
DateDiff returns a number of time intervals between two specified dates.
Overloads
  • DateDiff (intervalType, startDateTime, endDateTime)
  • DateDiff (intervalType, startDateTime, endDateTime, firstDayOfWeek)
  • Arguments
  • intervalType is a String expression that is the interval of time you use to calculate the difference between startDateTime and endDateTime.
  • startDateTime is the first DateTime value used in calculating the difference.
  • endDateTime is the second DateTime value used in calculating the difference.
  • firstDayOfWeek is an optional constant specifying the first day of the week. If not specified, crSunday is assumed.
  • Argument values
  • IntervalType values can be one of the following:
    Interval type value
    Description
    yyyy
    Extracts the year
    q
    Quarter (the result is 1, 2, 3 or 4)
    m
    Month (the result is from 1 to 12)
    y
    Day of year (1 to 365 or 366 in a leap year)
    d
    Day part of the date (1 to 31)
    w
    Day of week (1 to 7 with the result depending on firstDayOfWeek)
    ww
    Week of year (1 to 53 with firstDayOfWeek and firstWeekOfYear determining the exact days of the first calendar week of the year)
    h
    Extracts the hour part of the given DateTime (0 to 23)
    n
    Minute part (0 to 59)
    s
    Second part (0 to 59)
  • Returns
    A Number value.
    Action
    DateDiff returns a number of time intervals between two specified dates.
    Examples
    Use DateDiff with the "d" or "y" interval type parameter to find the number of days between two dates:
    DateDiff ("d", #10/7/1999#, #10/10/1999#)
    Returns 3.
    Use DateDiff with the "yyyy" interval type parameter to find the number of years difference between two dates. This use of DateDiff is the same as finding the difference between the year of endDateTime and the year of startDateTime.
    DateDiff ("yyyy", #10/7/1999#, #2/10/2005#)
    Returns 6.
    DateDiff ("yyyy", #12/31/1999#, #1/1/2000#)
    Returns 1 (a 1 year difference), even though there is only a 1 day difference between the dates.
    DateDiff ("yyyy", #1/1/1999#, #12/31/1999#)
    Returns 0 (a 0 year difference), even though there is a 364 day difference.
    Suppose that for the above examples, the first date is the date that you bought a mutual fund, and the second date is the date you sold it. The mutual fund company must send you an annual report for every year in which you owned units in the fund. Thus, you would get 7, 2 and 1 annual reports respectively, in the above cases.
    Use DateDiff with the "q" parameter to find the number of quarters (3 month periods) difference between two dates.
    DateDiff ("q", #10/6/1999#, #5/20/2003#)
    Returns 14.
    DateDiff ("q", #3/31/1999#, #4/1/1999#)
    Returns 1. The two dates are in adjacent quarters.
    DateDiff ("q", #1/1/1999#, #3/31/1999#)
    Returns 0. The two dates are in the same quarter.
    Suppose the mutual fund company in the "yyyy" example mailed out quarterly reports. Then it would need to mail out 15, 2 and 1 quarterly reports respectively, in the above cases.
    Use DateDiff with the "m" parameter to find the number of months difference between two dates.
    DateDiff ("m", #3/15/1999#, #7/13/1999#)
    Returns 4.
    Use DateDiff with the "w" parameter to calculate the number of weeks between two dates. For example, if startDateTime is on a Tuesday, DateDiff counts the number of Tuesdays between startDateTime and endDateTime not including the initial Tuesday of startDateTime. Note however that it counts endDateTime if endDateTime is on a Tuesday.
    DateDiff ("w", #10/19/1999#, #10/25/1999#)
    Returns 0.
    DateDiff ("w", #10/19/1999#, #10/26/1999#)
    Returns 1.
    Use DateDiff with the "ww" parameter to calculate the number of firstDayOfWeek's occurring between two dates. For the DateDiff function, the "ww" parameter is the only one that makes use of the firstDayOfWeek argument. It is ignored for all the other interval type parameters. For example, if firstDayOfWeek is crWednesday, it counts the number of Wednesday's between startDateTime and endDateTime. It does not count startDateTime even if startDateTime falls on a Wednesday, but it does count endDateTime if endDateTime falls on a Wednesday. For the examples, note that October 6, 13, 20 and 27 all fall on a Wednesday in 1999.
    DateDiff ("ww", #10/5/1999#, #10/29/1999#, crWednesday)
    Returns 4.
    DateDiff ("ww", #10/6/1999#, #10/29/1999#, crWednesday)
    Returns 3.
    DateDiff ("ww", #10/5/1999#, #10/27/1999#, crWednesday)
    Returns 4.
    For example, suppose you want to calculate the number of days between the order date and ship date, excluding Saturdays and Sundays:
    Local DateTimeVar d1 := {Orders.Order Date};
    Local DateTimeVar d2 := {Orders.Ship Date};
    DateDiff ("d", d1, d2) - 
         DateDiff ("ww", d1, d2, crSaturday) -
         DateDiff ("ww", d1, d2, crSunday)
    



    SAP BusinessObjects
    http://www.sap.com/sapbusinessobjects/
    Support services
    http://service.sap.com/bosap-support/
    Product Documentation on the Web
    http://help.sap.com/