DateAdd thought

[cinnie]

hi to all

I recently had a problem in my code that I've finally figured out. I'll
mention it here in case anyone has anything else to offer:

DateAdd("m", 1, "31-01-2010") has a value of 28-02-2010

If I apply DateAdd again to this result, I get...

DateAdd("m", 1, "28-02-2010") which has a value of 28-03-2010

But...

DateAdd("m", 2, "31-01-2010") has a value of 31-03-2010

In other words (this is what led to my coding error), applying DateAdd TWICE
for a ONE month interval is not the same as applying DateAdd ONCE for a TWO
month interval. This is probably obvious to all you gurus, but it sure had
me stuck!

 

[Dirk Goldgar]

hi to all

I recently had a problem in my code that I've finally figured out. I'll
mention it here in case anyone has anything else to offer:

DateAdd("m", 1, "31-01-2010") has a value of 28-02-2010

If I apply DateAdd again to this result, I get...

DateAdd("m", 1, "28-02-2010") which has a value of 28-03-2010

But...

DateAdd("m", 2, "31-01-2010") has a value of 31-03-2010

In other words (this is what led to my coding error), applying DateAdd
TWICE
for a ONE month interval is not the same as applying DateAdd ONCE for a
TWO
month interval. This is probably obvious to all you gurus, but it sure
had
me stuck!

Good observation. It's because DateAdd won't return an invalid date, so
adding 1 month to january 31st won't result in February 31st, but will be
adjusted back to the last day of February (adjusting as needed for leap
year).

 

[Marshall Barton]

I recently had a problem in my code that I've finally figured out. I'll
mention it here in case anyone has anything else to offer:

DateAdd("m", 1, "31-01-2010") has a value of 28-02-2010

If I apply DateAdd again to this result, I get...

DateAdd("m", 1, "28-02-2010") which has a value of 28-03-2010

But...

DateAdd("m", 2, "31-01-2010") has a value of 31-03-2010

In other words (this is what led to my coding error), applying DateAdd TWICE
for a ONE month interval is not the same as applying DateAdd ONCE for a TWO
month interval. This is probably obvious to all you gurus, but it sure had
me stuck!

In addition to Dirk's explanation of what happened in that
one case, check what happens when you use
DateAdd("m", 1, "3-4-2010") ' 3 April or 4 March??
on both your machine and on another machine that has its
regional setting set to USA date format. (Can you guarantee
every potential user's regional setting?)

The problem is that your date *string* is in an ambiguous
date style. To make it unambiguous Access recommends that
you use the USA style with date/time delimiters:
DateAdd("m", 1, #4-3-2010#) ' 3 April

I believe that the style:
DateAdd("m", 1, "2010-4-3")
or
DateAdd("m", 1, #2010-4-3#)
should also work, but relying on Access to convert a string
to a date correctly for any regional setting is too risky
for me so I recommend using the latter style.

 

[cinnie]

Thanks to both Dirk & Marshall.
--
cinnie

Good observation. It's because DateAdd won't return an invalid date, so
adding 1 month to january 31st won't result in February 31st, but will be
adjusted back to the last day of February (adjusting as needed for leap
year).

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)