MapInfo Pro Developers User Group

Happy #MapInfoMonday and #MapBasicMonday!

Today we will look at two functions that can help you add and subtract values to Date and DateTime values.

What's the problem?

This might be your initial question, right? You can already add values to a Date and DateTime value.

Let's see how that works. I'll use a basic example via the MapBasic window.

First, I initiate my Date variable, then I assign it the current date, and finally, I print the result to the Message window for inspection.
Dim dNow As Date
dNow = CurDate()
Print "Now: " + Str$(dNow)

In the Message window I can see this result:
Now: 30-05-2022

I use the Str$() to cast the date to a formatted date string using my regional settings.

Let's add a day to the current date, and a week, and see how that works.
As you can see I do this by adding a day to the current date value. For weeks, I add 7 days.
Print "Now + 1 day: " + Str$(dNow + 1)
Print "Now + 1 week: " + Str$(dNow + 7)

In the Message window I get this result that looks fine:
Now + 1 day: 31-05-2022
Now + 1 week: 06-06-2022

The problem arises when I want to add, or subtract, a month.

The Gregorian Calendar specifies the number of days in each month. But unfortunately, they are not of the same length. And to add to our problems, it also has what is referred to as a leap year. This is basically a year where February has 29 days instead of 28. But you all know that. There are rules to determine if a year is a leap year or not.

So in order to add a month to a given date, I need to know the current month. From this, I can determine the number of days of the month. If the month is February, I also need to determine whether it's a leap year or not.

And don't get me started on leap seconds that are occasionally applied to Coordinated Universal Time (UTC).

How do these functions help?

With MapBasic v2021, we introduced two new functions to help work with adding and subtracting date and time values from a Date or DateTime value.

• DateAdd() function: Use this function to add/subtract a provided value in date units from the date variable. You can call this function from the MapBasic window in MapInfo Pro.

• DateTimeAdd() function: Use this function to add/subtract a provided value in date/time units from the date-time variable. You can call this function from the MapBasic window in MapInfo Pro.

Both functions work by taking a Date or DateTime value as input in combination with a time value and a time unit. The function will then return the value after adding the given value using the specified time unit:

DateAdd/DateTimeAdd (input_date_or_datetime, value, units)

input_date_or_datetime (Date or DateTime): A date or date-time input parameter

value (Integer) : Positive/Negative value in date-time units

These units are supported, some are only supported for the function DateTimeAdd:

• hr (only DateTimeAdd)
• min (only DateTimeAdd)
• sec (only DateTimeAdd)
• day
• month
• year
• week
• century
• decade

Note that you can specify negative values to subtract values from your value and that you can specify other values besides 1. To specify a fortnight, you can specify either 2 "week" or 14 "day".

So to get back to our example from above on how you would add a month to a date value, here's the solution:
Print "Now + 1 month: " + Str$(DateAdd(dNow, 1, "month"))

The result looks like this:
Now + 1 month: 30-06-2022

And this also works with DateTime values.
Dim dtNow As DateTime
dtNow = CurDateTime()
Print "Now: " + Str$(dtNow)
Print "Now + 1 month: " + Str$(DateTimeAdd(dtNow, 1, "month"))

The result as seen in the Message window:
Now: 30-05-2022 12:01:37
Now + 1 month: 30-06-2022 12:01:37

And, you do not need to use the MapBasic window to benefit from these functions. You can as an example also use them through the Update Column dialog to update an existing Date or DateTime column.

Oh, and looking at the result, I can see it's lunchtime. See you in a week!

------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------