Date Math 102 – The DateAdd Function
What's the easiest way to get to a date in the future
or in the past? If today is February 12 and you need
to know the date 2 weeks from today, you’d add 14
(2 weeks) to 12 and get 26, so two weeks from
February 12 is February 26.
Now let’s imagine that you need to subtract two
weeks from February 12. Well 12 minus 14 = -2. Not
so simple now, is it? And what happens if you have
to determine the date 3 weeks from February 12? If
you add 12 + 21, you get 33, which takes you into
March. Now how many days does February have? Is
this year a leap year? Hmm – even more complex.
This month I'll review a built-in Access function
called DateAdd which greatly simplifies these complex
date math problems.
DateAdd – The Basics |
|
The DateAdd function takes three arguments:
interval, number, and date.
Interval refers to the period, or units, that
you want to add or subtract. The most commonly
used are:
Year – yyyy
Month – m
Day – d
Week – ww
For a full list of possibilities, search DateAdd in
Access Help.
Number refers to the number of units you
want to add or subtract from the date that you’re
interested in. If you want to subtract, use a
negative number.
Date refers to your reference date.
Here is an example showing how we could use the
DateAdd function to accomplish one of the
calculations mentioned earlier.
To add 14 days from February 12 we'd use the
DateAdd function and the format DateAdd(interval,
number, date):
DateAdd("d", 14, #2/12/2007#)
To try this yourself, open a code window (Alt-F11)
and type
? DateAdd("d", 14, #2/12/2007#)
in the immediate window. You will see the output to
this function is 2/27/2007.
There are a couple of important things to note. First,
the interval must be surrounded by quotes. The
second is that if you are supplying what could be
interpreted as a number you must bracket the date
with a date indicator symbol, typically the "#" or
pound symbol. This tells Access that the characters
within the pound sign should be interpreted as a
date; otherwise it will interpret the forward slash as
a division symbol and do the math to determine the
date. In our case it would divide 2 by 12 and then
divide that number by 2007. The starting point would
then be calculated as Dec 30, 1899.
|
Arcane Corners of DateAdd |
|
You can use this function for more than just date
math; you can also use it for time math. Additionally
you could also use this function for both date and
time math simultaneously.
If you've ever tried to add 37 minutes to a time, you
already know how complex this can be. The syntax is
the same, however you must pass in a time, and use
h, m, and s for hours, minutes and seconds to
designate the time.
For instance if you wanted to add 3 hours to the
time to see what time it is in California you’d use
DateAdd("h", 3, Now()). If you had a specific time
that you wanted to add or subtract to you put the
time in double quotes, like DateAdd("h",
3, "10:44AM"). The time format will take a variety of
inputs, so you’ll want to experiment to see which one
works best for you.
Use caution when doing time math: if there's also a
date involved, once you pass midnight the date will
move forward one day. This may be what you'd
expect, but if you aren’t paying attention, it might
catch you by surprise and trip you up.
|
Date Add Input Alternatives |
|
There are a couple of alternate methods of
representing the date in the DateAdd function. In
addition to #2/12/2007#, you could also type "Feb
12, 2007" surrounded by quotes. Or you could
surround the "2/12/2007" with quotes and that will
work as well.
I tend to use the pound symbol because that
convention can be used in queries. In addition,
quotes are frequently used in Access code (e.g. in
constructing strings), so fewer quotes makes for
cleaner code, in my opinion.
|
Conclusion and a look ahead |
|
This month we've tackled the easy part of date
math. More complex issues, to be addressed with
more powerful tools in a future issue, include
questions like “what is last day of next month?”
or “what date is one month before cousin Max’s third
birthday?”
|
Trap of the Month – Be careful when entering the Year |
|
If you're dealing with “current” dates, you may omit
the century. Entering the year as 2007 and 07 will
get the same result. If you enter 99, Access will
think you're talking about 1999. If you enter 10 for
the year Access will think that you're talking about
2010. You have to be careful here, because if you
enter 30 as the year, Access will think you mean
1930, when you may intend 2030.
As a general rule, your best bet is to use 4 digits for
the year. Different versions of Access interpret two
digit years differently. In some cases, Access will
interpret things in an unexpected manner, especially
during years with a February 29, which have a
surprising level of complexity.
|
|
Gratuitous Date Jokes |
|
Have you lived here all your life? Not yet.
How many birthdays has a 97 year old man had so
far? One
A boy is about to go on his first date, and is nervous
about what to talk about. He asks his father for
advice.
The father replies: "My son, there are three subjects
that always work. These are food, family, and
philosophy."
The boy picks up his date and they go to a soda
fountain. Ice cream sodas in front of them, they
stare at each other for a long time, as the boy's
nervousness builds.
He remembers his father's advice, and chooses the
first topic.
He asks the girl: "Do you like spinach?" She
says "No," and the silence returns.
After a few more uncomfortable minutes, the boy
thinks of his father's suggestion and turns to the
second item on the list. He asks, "Do you have a
brother?" Again, the girl says "No" and there is
silence once again.
The boy then plays his last card. He thinks of his
father's advice and asks the girl the following
question: "If you had a brother, would he like
spinach?"
|
|