shift(X;Y)

Returns the date-related value shifted by the amount specified.

Syntax

shift(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
  • date+time
Y any numeric type The amount to shift X, depending on the type of X
For example:
  • If X is a month value, Y specifies how many months to shift.
  • If X is a date value, Y specifies how many days to shift.
  • If X is a date+time value, Y specifies how many days (and fraction of a day) to shift.

If X is a date+time value, Y can be a decimal value; otherwise, it must be an integer.

If Y > 0, shift the date-related value forward. If Y < 0, shift the date-related value backward.

For more information, see Dates and time.

Return Value

Returns the same type of date-related value (integer or decimal) given as input, shifted by the amount specified.

Note: The type of date-related value that is given as input determines the shift (i.e., a year value will be shifted by years, a month value will be shifted by months, etc.)

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

Example using date value

In the Hourly U.S. Weather (1990) table (pub.demo.weather.hourly90), you can create a computed column that displays an integer value corresponding to the date value in the column named date shifted by 7 days:

<base table="pub.demo.weather.hourly90"/>
<willbe name="example" value="shift(date;7)" label="Shift`7 Days"/>
<colord cols="date,example"/>

For a date value of 01/01/90 (X=19900101), the result would be 01/08/90.

For a date value of 01/25/90 (X=19900125), the result would be 02/01/90.

Note: If the value in the column named date was a month value, the result would be shifted by 7 months.

Example using date+time value

You can create a computed column that displays the login date+time (using the logindatetime(X) function), and then create another computed column that displays that value shifted by some amount:

<base table="pub.demo.weather.hourly90"/>
<willbe name="logindatetimecol" value="logindatetime(-5)" label="Login`Date+Time" format="type:datehms24"/>
<willbe name="hoursexample" value="shift(logindatetimecol;.25)" label="Shift`6 Hours" format="type:datehms24"/>
<willbe name="daysandhoursexample" value="shift(logindatetimecol;1.5)" label="Shift`1 1/2 Days" format="type:datehms24"/>
<colord cols="logindatetimecol,hoursexample,daysandhoursexample"/>
Note: The computed columns are formatted using the datehms24 format type so that the date+time values will appear as dates and times and not as decimal numbers (i.e., 12/16/13_11:06:11 as opposed to -7,685.53737094584).
For a login date+time value of 12/16/13_11:06:11 (X=-7,685.53737094584):
  • The result in the hoursexample column would be 12/16/13_17:06:11. The login date+time is shifted by .25 days (or 6 hours).
  • The result in the daysandhoursexample column would be 12/17/13_23:06:11. The login date+time is shifted by 1.5 days (or 1 day and 12 hours).