Luke Hayler - SQL Server developer

Getting acquainted

The DateAdd() function. According to BOL this “Returns a date to which a specified time interval has been added”. The structure of the function is as follows:

DateAdd(interval, number, date)

It’s a Date!

Like most first dates, getting to grips with this function and understanding its ‘needs’ is a tricky process. Initially, you may have attempted to use the tried-and-trusted methods (aka ’syntax’), DateAdd(mm, 1, Now()). No. Ok, let’s try DateAdd(‘mm’, 1, now()). Nope. What about DateAdd(month, 1, Now()). Good luck with that…Yes. It’s a little mystifying.

Score!

There are a couple of ways to achieve the desired result with this function. 1.) use the DateInterval collection, or 2.) use the date part abbreviations. My recommendation? Use the DateInterval collection. Using the abbreviations, there is a chance that something will be calculated differently to what you’d expect (I explain a little further on). The DateInterval collection can be found in the Object Browser and looks like this:

Fig 1. A list of all the Intervals in the DateInterval collection

“So how do you use this in the function?” I hear you ask.

=DateAdd(DateInterval.Month, 1, Today()) which gives you a date 1 month from today.

This will work for all the intervals listed in Fig 1. above.

If you don’t want to use this method and instead want to use the abbreviations then there are a few things to be weary of. For instance, using DateAdd(“w”, 1, Today()) does not yield a date 1 week from today, but rather 1 day from today. This happens with a couple of other abbreviations as well. The reason is that “w” represents “weekday” not “week”. Here is an shortlist of the abbreviations that do work:

• Days ["dd"] – DateAdd(“dd”, 1, today())
• Weeks ["ww"] – DateAdd(“ww”, 1, today())
• Months ["mm"] – DateAdd(“mm”, 1, today())
• Years ["yyyy"] – DateAdd(“yyyy”, 1, today())

For a full list of abbreviations and further explanation, msdn delivers here.

Hopefully this clears up a little of the confusion surrounding the DateAdd() function and the intervals that need to be specified. Of course, if there are other methods that can be used or I’m all ears.

I like keep an open mind. When working with windows, one must.