Issues with dateadd function



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.



Wayne Morgan

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)

Allen Browne

The behavior makes sense to me.

One quarter before February 25 is November 25:
? DateAdd("q",-1,#02/25/2004#)
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#)

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




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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DateAdd thought 3
Help with the DateAdd function 9
DateAdd 4
elasped time problem 1
Last day of month in query 15
Error 3077 Recordset.FindFirst using dates 2
DateAdd question 1
Examining dates 5