Recently I started a series of articles where I’m covering datetime

calculations. In
DATETIME Calculations, Part 2 (InstantDoc #94819) I

discussed start and end of period calculations. For example, to calculate the

start date of the current month, I provided the following expression:

function calculates the difference in terms of months between the anchor and

the input datetime value (call that difference diff ). The DATEADD function

then adds diff months to the anchor datetime value. Because the anchor's

day unit is 1 (the first of the month), and you add whole months, you get the

first day of the month corresponding to the input datetime value. To get the

last day of the month, add diff plus one more month, and finally subtract one

day:

Subtracting one day produces the last day of the current month.

Peter Larsson suggested a simplification for the end of period calculation.

Use the constant -1 as the anchor date (December 31, 1899) instead of 0

(January 1, 1900). Since this
anchor date (-1) is the last day of a month (as

well as quarter and year), you
don’t need to add one month to get the first

day of the next month, and then
subtract one day to get the last day of the

current month. Here’s how the
simplified expression producing the last day

of the current month looks like:

the date part:

Cheers,

--

BG