periods(X;Y)

Returns the number of days, months, quarters, or years between two date-related values, depending on the types of those values.

Syntax

periods(X;Y)

Input

Argument Type Description
X any numeric type A scalar value or the name of a column containing date-related values
Valid values can be of the form:
  • date
  • month
  • quarter
  • year
Y any numeric type A scalar value or the name of a column containing date-related values
Valid values can be of the form:
  • date
  • month
  • quarter
  • year

For more information, see Dates and time.

Return Value

Returns the integer value corresponding to the number of days, months, quarters, or years between two date-related values given as input, depending on the types of those values.

This function returns the broadest result based on types of the arguments given. For instance:
  • When determining the period between a year and a date, the number of years will be returned.
  • When determining the period between a month and a date, the number of months will be returned.
  • When determining the period between a date and a date, the number of days will be returned.

If the input values are not in one of the supported formats listed above, the function returns an erroneous or illogical output.

Example

In the Hourly U.S. Weather (1990) table (pub.demo.weather.hourly90), you can create a computed column that displays the integer value corresponding to the period between the date value in the column named date and a particular date value. In this example, the four computed columns show the years, months, quarters, and days from 12/13/2013:

<base table="pub.demo.weather.hourly90"/>
<willbe name="yr_example" value="periods(date;'2013')" label="Periods`(Year)"/>
<willbe name="mo_example" value="periods(date;'201312')" label="Periods`(Month)"/>
<willbe name="qtr_example" value="periods(date;'20134')" label="Periods`(Quarter)"/>
<willbe name="day_example" value="periods(date;'20131213')" label="Periods`(Day)"/>
<colord cols="date,yr_example,mo_example,qtr_example,day_example"/>
For a date value of 04/30/90 (X=19900430):
  • The result for the first computed column would be 23.
  • The result for the second computed column would be 284.
  • The result for the third computed column would be 94.
  • The result for the fourth computed column would be 8,628.