Foxhound is the better* Database Monitor for SQL Anywhere. *better: More thorough, more relevant, more effective. ...more Alerts, more All Clears, more details, more control in your hands.

Date Arithmetic in PowerBuilder!

Our programmer predecessors coped with this by writing code that assumed "00" means "1900" so when the new millennium starts bank accounts will suddenly accumulate 100 years' worth of interest. Or sink to a zero balance, whichever is worse... it depends on your point of view.

Modern relational database systems have no such problem; even if you display dates with only two-digit years they are stored in the database with a full four-digit year.

That doesn't mean everything is sweetness and light, however. We're still faced with every-day problems involving date arithmetic. It's really too bad the Cosmos never adopted the Metric System where every year has 10 months, every month has 10 days, every day has 10 hours and so on. No, we have to deal with astronomical intricacies like February having 28 days unless the year is divisible by 4 in which case it has 29 days, except where the year is divisible by 100 (28 days again) but not if it's divisible by 400 (29 days).

Simple questions like "What's the last day of the month?" become interesting challenges. Figure 1 shows the desired result.

Figure 1: Calculate Last Date in Month

A helpful rule of thumb is to ask "How can I use and abuse the RelativeDate() function to get the answer I need?" That's because the RelativeDate() function handles date arithmetic in units of days without stumbling over Leap Years and other problems.

It's easy to calculate the last day in the previous month by calling day() and RelativeDate() to subtract the day number from the date:

   ld_last = RelativeDate ( ls_this, -day ( ld_this ) )

So what we need now is some date in the next month in order to use this trick to calculate the last date in this month. We can't just add some number of days to the first date because the result might be still in this month, or two months in the future depending on the first date and the number of days we pick.

What we can do is calculate the last date in the previous month as above, then add some number like 35 to get a date in the next month, and finally reapply the trick to get the answer. The code is shown in Figure 2.

Figure 2: f_last_date_in_month

/*
Description:
   Compute the date of the last day in the month
   of the parameter date.

Arguments:
   ad_parm - Date for which the last day in the
   month is calculated.

Returns:
   date - The date of the last day in the month.

Example - Compute the last day in the current month:
   ld_last = f_last_date_in_month ( today() )
*/

date ld_last_date_in_previous_month
date ld_date_in_next_month

// Compute the last date in previous month by the trick
// of subtracting the day number from the date.
// E.g., 1996-06-15 - 15 becomes 1996-05-31.

ld_last_date_in_previous_month &
   = RelativeDate ( ad_parm, -day ( ad_parm ) )

// Compute a date guaranteed to be in the next month.
// E.g., 1996-05-31 + 35 becomes 1996-07-05.

ld_date_in_next_month &
   = RelativeDate ( ld_last_date_in_previous_month, 35 )

// Compute the return value by the same trick,
// applied to the future date.
// E.g., 1996-07-05 - 05 becomes 1996-06-30.

return RelativeDate ( ld_date_in_next_month, &
                      -day ( ld_date_in_next_month ) )

Functions like day(), RelativeDate() and DaysAfter() are invaluable when performing date arithmetic. How they can be used to solve your problem might not be immediately obvious, but they can be combined in weird and wonderful ways to calculate just about any result.