Strings representing dates can be manipulated with + and - just like numbers, and have a few functions to help work with them. Dates are seen internally as strings, and should be enclosed in quotes when trying to use literal dates; they can be given in your local / national format. The following operators are supported:

  • date + count --> date string representing date + count days
  • date - count --> date string representing date - count days
  • date1 - date2 --> integer representing elapsed days (can be negative if date2 is a later date)

Note IconNote that when using standard operators, the operation always uses days as the time unit. To use other time units, you must use the supplied functions.

date_add( date, count[, unit_string] )

This adds the specified count of time units to the given date, and returns a new date.

date_subtract( date, count[, unit_string] )

This subtracts the specified count of time units from the given date, and returns a new date.

date_subtract( date1, date2[, unit_string] )

This subtracts the specified date from the first, and returns the elapsed time in the specified units. The result is always the count of whole units that have elapsed. So if you specify dates 20 days apart and ask for months, no whole months have elapsed and so 0 will be returned.

format_date( date, format_string )

This reformats the date string into the specified format for friendlier presentation. format_string can be #SHORTDATE, #LONGDATE, #CUSTOMDATE[date_format] just as they are for formatting dates with format specifiers. The hashes are optional here; for CUSTOMDATE you can simply supply the format such as "%Y-%m-%d".

To make it easier to select date formats for the second parameter of format_date(), you can apply formatting whenever you are inside speech - as long as you have an odd number of double quotes to the left of the cursor position the format can be inserted / updated as if the speech mark on the left were a variable. For example, if you type

format_date($Today, "

(note the trailing double quote) you can now select a date format and click Apply. It will be inserted at the cursor position, right after the quote. Placing the cursor anywhere within that format string will reflect the current format and allow you to change it, just like it does for a variable suffix.

date_part( date, units_string )

This returns an integer representing just the portion of the date you request.

make_date( year, month, day[, hour, minute, second] )

This returns a date string built from the parts you supply. Month is 1 - 12, day is 1 - 31.

Notes

  • Unless otherwise specified, any date strings are returned in Access' timestamp format; use format_date() to change to a more familiar style.
  • units_string is "Y", "M", "D" for years / months / days or the relevant portion of a date, or "h", "m", "s" for hours / minutes / seconds or the relevant portion of a date. Where supported, if you omit the unit string (or supply an unrecognised one), it is assumed to be "D" for days.