Arithmetic operations with date and time variables

Most date and time variables are stored internally as the number of seconds from a particular date or as a time interval and therefore can be used in arithmetic operations. Many operations involving dates and time can be accomplished with the extensive collection of date and time functions.

  • A date is a floating-point number representing the number of seconds from midnight, October 14, 1582. Dates, which represent a particular point in time, are stored as the number of seconds to that date. For example, October 28, 2007, is stored as 13,412,908,800.
  • A date includes the time of day, which is the time interval past midnight. When time of day is not given, it is taken as 00:00 and the date is an even multiple of 86,400 (the number of seconds in a day).
  • A time interval is a floating-point number representing the number of seconds in a time period, for example, an hour, minute, or day. For example, the value representing 5.5 days is 475,200; the value representing the time interval 14:08:17 is 50,897.
  • QYR, MOYR, and WKYR variables are stored as midnight of the first day of the respective quarter, month, and week of the year. Therefore, 1 Q 90, 1/90, and 1 WK 90 are all equivalents of January 1, 1990, 0:0:00.
  • WKDAY variables are stored as 1 to 7 and MONTH variables as 1 to 12.

You can perform virtually any arithmetic operation with both date format and time format variables. Of course, not all of these operations are particularly useful. You can calculate the number of days between two dates by subtracting one date from the other—but adding two dates does not produce a very meaningful result.

By default, any new numeric variables that you compute are displayed in F format. In the case of calculations involving time and date variables, this means that the default output is expressed as a number of seconds. Use the FORMATS (or PRINT FORMATS) command to specify an appropriate format for the computed variable.

Example

DATA LIST FREE /Date1 Date2 (2ADATE10).
BEGIN DATA
6/20/2006 10/28/2006
END DATA.
COMPUTE DateDiff1=(Date2-Date1)/60/60/24.
COMPUTE DateDiff2=DATEDIFF(Date2,Date1, "days").
COMPUTE FutureDate1=Date2+(10*60*60*24).
COMPUTE FutureDate2=DATESUM(Date2, 10, "days").
FORMATS FutureDate1 FutureDate2 (ADATE10).
  • The first two COMPUTE commands both calculate the number of days between two dates. In the first one, Date2-Date1 yields the number of seconds between the two dates, which is then converted to the number of days by dividing by number of seconds in a minute, number of minutes in an hour, and number of hours in a day. In the second one, the DATEDIFF function is used to obtain the equivalent result, but instead of an arithmetic formula to produce a result expressed in days, it simply includes the argument "days".
  • The second pair of COMPUTE commands both calculate a date 10 days from Date2. In the first one, 10 days needs to be converted to the number of seconds in ten days before it can be added to Date2. In the second one, the "days" argument in the DATESUM function handles that conversion.
  • The FORMATS command is used to display the results of the second two COMPUTE commands as dates, since the default format is F, which would display the results as the number of seconds since October 14, 1582.

For more information on date and time functions, see Date and time functions .

Conditional statements and case selection based on dates

To specify a date as a value in a conditional statement, use one of the data aggregation functions to express the date value. For example,

***this works***.
SELECT IF datevar >= date.mdy(3,1,2006).
***the following do not work***.
SELECT IF datevar >= 3/1/2006. /*this will select dates >= 0.0015.
SELECT IF datevar >= "3/1/2006" /*this will generate an error.

See the topic Aggregation functions for more information.