Date/Time Functions – Part 2

(The DateAdd Function)

 

The DateAdd function enables you to add a specified time interval to a date.

 

Syntax:

DateAdd(interval, number, date)

 

The DateAdd function syntax has these parts:

 

Part

Description

interval

Required. String expression that is the interval of time that you want to add.

 

The string expression can be any of the following:

 

Expression   Description                    

"yyyy"                                               Year                              

"q"                                                     Quarter                          

"m"                                                    Month                            

"y"                                                     Day of year                    

"d"                                                     Day                               

"w"                                                    Weekday                       

"ww"                                                  Week    

"h"                                                     Hour                              

"n"                                                     Minute                           

"s"                                                     Second                          

 

Note: To add days to date, you can use either "d", "y", or "w". Be advised that the term "weekday" ("w") has nothing to do with skipping weekend days; i.e., it is NOT equivalent to "business days". Applications that would require skipping weekend days (like Saturday and/or Sunday) would have to be custom coded.

 

number

Required. Numeric expression that is the number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past).

 

date

Required. Date value that you want to evaluate.

 

 

To demonstrate DateAdd, set up a "Try It" project, and place the following code in the cmdTryIt_Click event:

 

Private Sub cmdTryIt_Click()

 

    Print "Current date/time is: "; _

          Format$(Now, "Long Date"); _

          Spc(1); _

          Format$(Now, "Long Time")

    Print

    Print "*** DateAdd Function Examples (with 3 as the number argument) ***"

    Print "Using 'yyyy':"; Tab(20); DateAdd("yyyy", 3, Now)

    Print "Using 'q':"; Tab(20); DateAdd("q", 3, Now)

    Print "Using 'm':"; Tab(20); DateAdd("m", 3, Now)

    Print "Using 'y':"; Tab(20); DateAdd("y", 3, Now)

    Print "Using 'd':"; Tab(20); DateAdd("d", 3, Now)

    Print "Using 'w':"; Tab(20); DateAdd("w", 3, Now)

    Print "Using 'ww':"; Tab(20); DateAdd("ww", 3, Now)

    Print "Using 'h':"; Tab(20); DateAdd("h", 3, Now)

    Print "Using 'n':"; Tab(20); DateAdd("n", 3, Now)

    Print "Using 's':"; Tab(20); DateAdd("s", 3, Now)

    Print

    Print "*** DateAdd Function Examples (with -3 as the number argument) ***"

    Print "Using 'yyyy':"; Tab(20); DateAdd("yyyy", -3, Now)

    Print "Using 'q':"; Tab(20); DateAdd("q", -3, Now)

    Print "Using 'm':"; Tab(20); DateAdd("m", -3, Now)

    Print "Using 'y':"; Tab(20); DateAdd("y", -3, Now)

    Print "Using 'd':"; Tab(20); DateAdd("d", -3, Now)

    Print "Using 'w':"; Tab(20); DateAdd("w", -3, Now)

    Print "Using 'ww':"; Tab(20); DateAdd("ww", -3, Now)

    Print "Using 'h':"; Tab(20); DateAdd("h", -3, Now)

    Print "Using 'n':"; Tab(20); DateAdd("n", -3, Now)

    Print "Using 's':"; Tab(20); DateAdd("s", -3, Now)

 

End Sub

 

For this example, you will need to adjust the size of your form to make it long enough to display all of the output lines.

 

Run the project and click the "Try It" button. The output should look similar to the following:

 

 

 

Download the VB project code for the example above here.

 

Adding a Time to a Date

We saw previously that you can use the DateValue and TimeValue functions to extract the date portion or time portion, respectively, from a Date field that contains both Date and Time data. What if you need to do the reverse – where you have two separate date fields, one with date data only and one with time data only, and you need to put them together in one date field that contains both date and time data?  Although there is not a specific function to do this, it can be accomplished with the following code (presented in the context of a "Try It" example):

 

Private Sub cmdTryIt_Click()

 

    Dim dtmDateOnly As Date

    Dim dtmTimeOnly As Date

    Dim dtmBoth As Date

 

    Print "Current Date/Time is: "; Now

 

    dtmDateOnly = DateValue(Now)    ' isolate date portion

    dtmTimeOnly = TimeValue(Now)    ' isolate time portion

   

    Print "dtmDateOnly = "; Format$(dtmDateOnly, "m/d/yyyy h:nn:ss AM/PM")

    Print "dtmTimeOnly = "; Format$(dtmTimeOnly, "m/d/yyyy h:nn:ss AM/PM")

 

    dtmBoth = dtmDateOnly  ' initialize the combined var with the date only var

    Print "dtmBoth = "; Format$(dtmBoth, "m/d/yyyy h:nn:ss AM/PM")

   

    dtmBoth = DateAdd("h", Hour(dtmTimeOnly), dtmBoth)  ' add the hour to the combined var

    Print "dtmBoth = "; Format$(dtmBoth, "m/d/yyyy h:nn:ss AM/PM")

   

    dtmBoth = DateAdd("n", Minute(dtmTimeOnly), dtmBoth) ' add the minute to the combined var

    Print "dtmBoth = "; Format$(dtmBoth, "m/d/yyyy h:nn:ss AM/PM")

   

    dtmBoth = DateAdd("s", Second(dtmTimeOnly), dtmBoth) ' add the seconds to the combined var

    Print "dtmBoth = "; Format$(dtmBoth, "m/d/yyyy h:nn:ss AM/PM")

 

End Sub

 

Run the project and click the "Try It" button. The output should look similar to the following:

 

 

Download the VB project code for the example above here.