Numbers are probably the most important scalar objects of business applications, but dates may be a close second.

Dates Are Harder Than Numbers

Numbers are simple; they line up.  Commands manipulate them without units.  Dates are more complicated because we group them on calendars.  We give them names.  We invent cycles and use the same names over and over; how many days have you lived through called “Monday?”  (Versus how many times do you hit the number seven when you’re counting to one hundred?)

We group days into weeks, but different organizations start their weeks on different days.  We group days into months unevenly; either 28, 29, 30, or 31 at a time.  We group either 365 or 366 days into a year.  We group weeks into years (52 or 53).  We group 12 months into a year.  We have pay periods; bi-weekly (26 or 27 per year), semi-monthly (24 per year, 13, 14, 15, or 16 days long), or monthly.  We have days and we have work days, which are all days except weekends and holidays.  Weekends are sometimes different from Saturday and Sunday.  We have different rules for different significant events; Halloween is always on October 31, Thanksgiving is always on the fourth Thursday of November.  U.S. election day is the first Tuesday following the first Monday in November.  We have fiscal years, which can end on any day of the year, though usually at the end of a month.  We have fiscal quarters which are usually three months but are sometimes 13 weeks (which can lead to fiscal months which are 4, 4, and 5 weeks long).

SQR Has Date Variables

It may be news to some people that SQR version 4.0 introduced date variables in November 1996.  Lots of Peoplesoft SQR programs use character variables for dates and lots of people learned SQR by studying that code.  Date variables look like string variables – they begin with the “$” symbol – but we use the declare-variable command to differentiate them.  Arrays can have date fields too.

There are several built-in date functions.  In these descriptions, $date and $date2 are date variables.  All other variables starting with the “$” symbol are string variables.  The $unit variable can be a sting containing these words: second, minute, hour, day, week, month, quarter, or year.

  • dateadd($date, $unit, #quantity) returns a date that is earlier or later than $date.  The #quantity is a positive or negative number.  I sometimes use ‘month’ here because it’s a convenient way to move from the first of one month the first of another month without worrying about the lengths of the month or the wraparound to a different year.
  • datediff($date, $date2, $unit) returns the number of intervals (type of interval specified by $unit) from $date2 until $date.  We can think of it as “$date minus $date2,” which can be positive or negative, a whole number or a number with a fraction.  I don’t use ‘month’ here because months have variable lengths.
  • datenow() does not take an argument and returns the date and time it is executed.
  • datetostr($date, $mask) returns a string with the elements of the date and time specified by the $mask argument.  The $mask argument is formatted like the edit masks documented with the print command.  This goes far beyond formatting; datetostr can extract the quarter of the year (1 – 4), the Roman numeral of the month, the week of the year (1 – 53), the week of the month (1 – 5), the day of the year (1 – 366), the Julian day (number of days since January 1, 4713 BC), or the number of seconds past midnight (0 – 86399).
  • edit($date, $mask) operates on dates like datetostr.
  • strtodate($string, $mask) returns a date calculated by interpreting the $string according to the format of $mask.

There are also many commands that support dates.

  • Date variables used by SQL statements in begin-select or begin-sql blocks are automatically reformatted for the database.
  • Concat, string, and unstring can reformat a date to a string according to the programmer’s mask, the alter-locale specification, or the sqr.ini file.
  • Display, print, and show output a date variable according to the programmer’s mask, the alter-locale specification, or the sqr.ini file.
  • Evaluate, extract, find, get, if, let, put, and while work with date variables and literals.
  • Input can validate the user’s formatting.
  • Move converts dates to strings like datetostr or edit, or strings to dates like strtodate.

Basic Date Formulae

Our work often screeches to a dead halt when we have to navigate the calendar.  What was the numeric value of Wednesday?  Is the first day of the week a one or a zero?  What’s the last day of the current fiscal quarter?  What’s the first day of next week?    What is the date of next Thanksgiving?  It’s not fun to puzzle out these formulae and it’s even worse to test them.  Wouldn’t it be great to just insert a substitution variable like {month_end}?

First, let’s document the days of the week so we can use variables like {Tuesday} in our formulae.  That’s clearer than using “3,” isn’t it?

#define Sunday        1
#define Monday        2
#define Tuesday       3
#define Wednesday     4
#define Thursday      5
#define Friday        6
#define Saturday      7

Sometimes we need to add or subtract a week to get to the date we want.  Use a substitution variable rather than “7” or “-7”, which make the reader wonder “why add seven?”  Sure, everybody knows that there are seven days in a week, but there may be other significance to the number seven (The number of continents?  The number of business units in your organization?), so let’s clarify which reason we use “7.”

#define week_in_days  7
#define year_in_months 12

Create a {today} variable because $current-date and datenow() include time.  Note that {today} is formatted for SQR date variables.

#define today         edit(datenow(), 'yyyymmdd')
#define tomorrow      edit(dateadd(datenow(), 'day', 1), 'yyyymmdd')
#define yesterday     edit(dateadd(datenow(), 'day', -1), 'yyyymmdd')

These variable names describe their contents but not their uses.  We might use them like this:

#define january1      edit(datenow(), 'yyyy0101')
#define december31    edit(datenow(), 'yyyy1231')

let $pay_begin_date = {january1}

It can be risky to “help” our users too much.  One business analyst might assure us “I always come to work on Monday and want to process this data as of the previous Friday.”  Sure, we know better than to write “as of date is three days before system date,”  but do we want to lock in a “last Friday” calculation?  What if the analyst is on vacation for a week, and wants to run the report retroactively?  I’ve learned to ask the user for an as-of date rather than “helpfully” calculating it.  But, if you’re sure that’s what you want:

#define last_sunday   edit(dateadd(datenow(), 'day', {Sunday}-to_number(edit(datenow(), 'd'))), 'yyyymmdd')
#define next_monday   edit(dateadd(datenow(), 'day', {Monday}+{week_in_days}-to_number(edit(datenow(), 'd'))), 'yyyymmdd')

Here are the companions to the January 1 / December 31 formulae; the first and last of the current month, and the first and last of the current quarter.

#define month_start   edit(datenow(), 'yyyymm01')
#define month_end     edit(dateadd(dateadd(datenow(), 'day', -to_number(edit(datenow(), 'dd'))), 'month', 1), 'yyyymmdd')
#define quarter_start edit(datenow(), 'yyyy') || substr( '   0101040107011001', 4 * to_number(edit(datenow(), 'q')), 4)
#define quarter_end   edit(datenow(), 'yyyy') || substr( '   0331063009301231', 4 * to_number(edit(datenow(), 'q')), 4)

Fiscal Date Formulae

Many organizations have fiscal (accounting) years different from calendar years; the last day of their fiscal year is different from December 31.  This blog entry was written in calendar year “2009.”  If our fiscal year ended on April 30, 2009, we probably would call it “2009” for the calendar year in which it ended.  We could call it “2008” for the calendar year in which it started and which contained most of the fiscal year.  We could call it “08/09” to avoid confusion.  These examples name the fiscal year as the calendar year in which it ends.  Let’s define the fiscal year as follows.

#define fiscal_end_mmdd  '0430'
#define fiscal_end_month 4

What is the current fiscal year?

let #year = to_number(edit(datenow(), 'yyyy')) + cond(edit(datenow(), 'mmdd') <= {fiscal_end_mmdd}, 0, 1)
move #year to $year 9999

What are the first and last dates of the fiscal year ($year)?

let $last_date = strtodate($year || {fiscal_end_mmdd}, 'yyyymmdd')
let $first_date = dateadd(dateadd($last_date, 'year', -1), 'day', 1)

Fiscal month calculations need modular arithmetic.  Imagine an analog clock.  The hours from 1 to 12 are spaced around a circular dial.  If it’s 9:00 and we add six hours, it will be 3:00, even though the number 3 is less than the number 9.  Modular arithmetic is slightly different, requiring a clock numbered from 0 to 11 for “modulo 12.”  There are two ways to use modular arithmetic in SQR:

let #x = mod(#a, 12)
let #x = #a % 12

If the last month of the fiscal year (in this case, April) is 12, and the first month of the fiscal year (in this case, May) is 1, what is the number of the current fiscal month?

let #fiscal_month = (to_number(edit(datenow(), 'mm')) + {year_in_months} - 1 – {fiscal_end_month}) % {year_in_months} + 1

What are the first and last dates of a particular fiscal month?

let $calendar_month = edit(({fiscal_end_month} + #fiscal_month) % {year_in_months}, ‘09’)
let $first_date = strtodate($year || $calendar_month || '01', 'yyyymmdd')
let $last_date = dateadd(dateadd($first_date, 'month', 1), 'day', -1)

Note that I used two dateadd() functions to get from the first of the month to the last date of the month.  It is simpler than trying to add some number of days (27, 28, 29, or 30), or add one to the calendar month (which could be 12, taking us to a new $year and needing to be reset to 1).

What is the current fiscal quarter (1 – 4)?

let #current_fiscal_quarter = edit(dateadd(datenow(), ‘month’, {year_in_months} – {fiscal_end_month}), ‘q’)

In this case, the fiscal end-month is 4, and twelve minus four is eight.  We add eight months to the current date and extract the calendar quarter.  If it’s May, June, or July, we move forward to January, February, or March, which is first quarter – we’re not looking at the year.  If it’s February, March, or April, we move forward to October, November, or December, which is the fourth quarter.

What are the first and last dates of a particular fiscal quarter?

Remember our hard-coded substr() lookup for calendar quarters?  We can’t just change the hard-coded strings to get fiscal quarters – though that is part of the answer – because we could be in a fiscal quarter that starts in one calendar year and ends in another.

First, let’s get strings containing the current calendar year ($curr_yyyy = ‘2009’), the previous year ($prev_yyyy = ‘2008’), and the next year ($next_yyyy = ‘2010’).  We also need strings for the month and day that start the quarter and end the quarter (e.g. third quarter starts on ‘1101’ and ends on ‘0131’).

let #curr_yyyy = edit(datenow(), ‘yyyy’)
let $prev_yyyy = edit(#curr_yyyy – 1, ‘9999’)
let $curr_yyyy = edit(#curr_yyyy, ‘9999’)
let $next_yyyy = edit(#curr_yyyy + 1, ‘9999’)
let $curr_mmdd = edit(datenow(), ‘mmdd’)
let $start_mmdd = substr( '   0501080111010201', 4 * #fiscal_quarter, 4)
let $end_mmdd   = substr( '   0731103101310430', 4 * #fiscal_quarter, 4)

Now, when is the third quarter of the current fiscal year?  If we ask the question between January 1, 2009 and April 30, 2009, the answer is November 1, 2008 through January 31, 2009.  If we ask the question between May 1, 2009 and December 1, 2009, the answer is November 1, 2009 through January 31, 2010.

if edit(datenow(), ‘mmdd’) <= {fiscal_end_mmdd}
let $start_yyyy = cond($start_mmdd <= {fiscal_end_mmdd}, $curr_yyyy, $prev_yyyy)
let $end_yyyy   = cond($end_mmdd   <= {fiscal_end_mmdd}, $curr_yyyy, $prev_yyyy)
let $start_yyyy = cond($start_mmdd <= {fiscal_end_mmdd}, $next_yyyy, $curr_yyyy)
let $end_yyyy   = cond($end_mmdd   <= {fiscal_end_mmdd}, $next_yyyy, $curr_yyyy)