Issues with dateadd function

  • Thread starter Thread starter mgc7999
  • Start date Start date
M

mgc7999

Ran into an issue when using the dateadd function to
calculate intervals backwards. No matter which interval
(quarter, month, year), when starting from a short end of
month date (ie April 30, Feburary 28, etc), the intervals
fail to compensate for long end of months (ie. Janurary
31). For example dateadd("q",-1,#02/29/04) gives you the
result of 11/29/03 instead of 11/30/03. Is this a known
issue, and what kind of work around do you use. I need
to be able to accurately go back a quarter, without
having an extra day or two added to the months.
 
It doesn't appear that "q" goes to an actual "quarter", it just goes 3
months. The short date month doesn't appear to have anything to do with it,
if you used 2/15/2004, it would give you 11/15/2003. If you want the last
day of the month for 3 months ago, you may find DateSerial easier to use.

Current Date: 2/29/2004
End of month, 3 months ago, 11/30/2003

DateSerial(2004, 2-2, 0)

The 2-2 comes from 2 for the previous month, subtract 3, and add 1 to go to
the month after that (in this case December). This is followed by the 0
which is 1 for the first day of the month (December 1st) minus one day
(11/30/2003). So, what is actually being done is

DateSerial(YearOfDate, MonthOfDate - 3 + 1, 1 - 1)
 
The behavior makes sense to me.

One quarter before February 25 is November 25:
? DateAdd("q",-1,#02/25/2004#)
11/25/2003
The result for Feb 29 is consistent.

Similarly, one month before February 29 is correctly understood as January
29, i.e.:
? DateAdd("m",-1,#02/29/2004#)
01/29/2004

If you want the last day of the previous quarter, you could begin with the
first day of the next quarter, and subtract 1 from the result:
? DateAdd("q",-1,#03/01/2004#) - 1
11/30/2003
 
We actually want 2/15/2004 to give 11/15/2003. It's just
when starting from the end of one month, we would want it
to end on the end of the month 3 months ago, not on the
next to the last day of that month. Essentially, the
only time the dateadd function would end up on the last
day of a long month when calculating back is if you're
calculating back from a long month. Looks like it's
something we'll just have to come up with something that
will give the results we need.
 
Back
Top