One of our readers, John, recently posted a question on the SQR Dates blog entry:

“I need to subtract from date in the format years, months, days. For example, I want to subtract 5 years, 2 months and 3 days from the current date.  How can this be done in SQR?”

Multiple DATEADD Syntax

He already read about the $CURRENT-DATE built-in variable and the DATEADD() and DATENOW() functions on that post, so I think he was hoping that there was another function, perhaps a YMD_ADD() function that he could use like this:

let $new_date = ymd_add($current-date, -5, -2, -3)

It’s not that easy.  Interpreting his question as being about syntax, the short answer is to use the DATEADD function as follows:

    date $new_date

let $new_date = dateadd(dateadd(dateadd($current-date, 'year', -5), 'month', -2), 'day', -3)

We can address the programming syntax easily, but there are time bombs hidden in this problem that require systems analysis.  I mean “time bombs” in two senses; danger inherent in calculations relating to time, and danger that may not be apparent until the future.

Subtracting Months and Years

The first issue is to clarify the meaning of adding or subtracting months and years.

What should happen when we subtract 5 years?  Is that the same as subtracting 1825 days (365 times 5), or should it include the number of leap days in that five year period:

  • 0 when subtracting 5 years from 1902 because 1900 wasn’t a leap year (only century years divisible by 400 are leap years in the Gregorian calendar).
  • 1 when subtracting 5 years from 2015 to include the leap day in 2012.
  • 2 when subtracting 5 years from March 2013 through February 2014 to include the leap days in 2008 and 2012.

When we add or subtract years, rather than add or subtract some multiple of 365 days, we probably want the new date to have the same month and day as the old date with a different year.
What should happen when we subtract 2 months?  Is it the same as subtracting 61 days (the average month is 365.25/12 or 30.4375 days long, so two months will average about 61 days).  Or should it depend on which two consecutive months are subtracted, meaning either 59, 60, 61, or 62 days.  We probably want the new date to have the same day as the old date, but to have a different month.

Non-existence of Dates

February 29, 2012 minus 5 years cannot be February 29, 2007.  Should it be February 28, 2007 or March 1, 2007?  What will the DATEADD function produce?  That last question is not rhetorical.  I do not have access to SQR anymore and I cannot test DATEADD.

August 31 minus 2 months cannot be June 31.  Should it be June 30 or July 1?  What will the DATEADD function produce?

Order of Operation

I wrote:

let $new_date = dateadd(dateadd(dateadd($current-date, 'year', -5), 'month', -2), 'day', -3)

Is that right?  Or should we write:

let $new_date = dateadd(dateadd(dateadd($current-date, 'day', -3), 'month', -2), 'year', -5)

What’s the best order of operation?  There are six choices: year-month-day, year-day-month, month-year-day, month-day-year, day-year-month, or day-month-year.  And they can all produce a different result set when run every day for a four year period!

Number arithmetic is commutative.  If we start with the number 20 and subtract 5, 2, and 3, we get 10, no matter which order we do the subtraction (e.g. 20 – 5 – 3 – 2 = 20 – 2 – 3 – 5).

I estimate that date arithmetic is not commutative 8 – 9% of the time because the time units are variable.  Years can be 365 or 366 days long.  Months can be 28, 29, 30, or 31 days long.

July 4 minus 4 days = June 30
June 30 minus 1 month = May 30

July 4 minus 1 month = June 4
June 4 minus 4 days = May 31

March 1, 2012 minus 2 days = February 28, 2012
February 28, 2012 minus 1 year = February 28, 2011

March 1, 2012 minus 1 year = March 1, 2011
March 1, 2011 minus 2 days = February 27, 2011

The order in which we subtract changes the results for certain dates but not for others.  There can be problems if we subtract years from February 29, subtract months from day 29, 30, or 31, or subtract enough days to cross a month boundary.  The risk can arise within the process; our start day might be safe, but our first subtraction may produce a problematic interim value.

What the User Wants

Since John referred to the “current date” in his question, I envision an application that runs periodically and needs a date from the past.  I think the description “subtract 5 years, 2 months, and 3 days” may have a business reason for that interval, but it is also acceptable to say “subtract 1889 (or whatever) days.”

I think there’s an unstated requirement that as the current date advances, the day in the past should advance at the same pace.  If the program runs one day later, the date from the past that it calculates should also be one day later.

Perhaps we should use this code:

! Comment:
! we use a date that is (approximately) 5 years, 2 months, and 3 days in the past,
! which gives our customers a 2 month grace period, plus time for the mail, after
! the expiration of their five year purchase agreements

let $new_date = dateadd($current-date, 'day', -1889)