Functions for Calculating and for Displaying Date/Time Values in Access

Functions for Calculating and for Displaying Date/Time Values in Access from Microsoft covers a whole bunch of data and time topics.

It has functions for everything from the last day of the current quarter to the first day of the current week. It also has some functions for working with intervals, although you'll see they differ slightly from my advice on the right way to store intervals. They store them in a date/time variable and then convert to numbers of days and hours for display. In my post Summing Times, or Date/Time Data: An Instant or a Duration? I recommended storing intervals as a number of minutes or seconds (or days). I stand by that recommendation. I think the example Microsoft posts is over-simplified. They read total hours from a time card table- it would be more likely to read start and end time and then to calculate an interval as a number of minutes using Datediff().

Here's the list of specific date functions they provide:

• The current month
• The next month
• The last day of the current month
• The last day of the next month
• The first day of the previous month
• The last day of the previous month
• The first day of the current quarter
• The last day of the current quarter
• The first day of the current week (assuming Sunday = day 1)
• The last day of the current week
• The first day of the current week (using settings in Options dialog box)
• The last day of the current week