SQR Dates

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)
or
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)
else
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)
end-if

18 Comments

  1. Edward deShelly says:

    First!

  2. Bob Josephson says:

    Some places have a need to know dates such as Good Friday, Rosh Hashanna, or Chinese New Year (e.g. religious organizations that give those days as paid holidays, or secular companies that allow floating holidays to be taken only on real holidays). Does SQR have anything to help identify dates on non-Gregorian calendars?

    • administrator says:

      SQR has a few functions for the Japanese calendar, but I haven’t seen anything else. Of course, I’m viewing the documentation supplied in English for the American market, so there may be a whole world I’ve never seen.

      • Bob Josephson says:

        Well, there’s an opportunity for a blog post: how to calculate those dates.

        • administrator says:

          We’ll see if the demand materializes.

          • Edward deShelly says:

            Well, I agree with Bob. You should do a blog entry about calculating non-Gregorian dates.

            So now you have two people asking for it. How much more demand do you need?

        • Norma says:

          Normally programs code to the appropriate calendar to get the dates for holidays, bank holidays, country, et. al. These are delivered in Peoplesoft. For example, financials open periods shows by product current open accounting periods and fiscal year, then you just go to the detail calendar being used and get dates or use the range.

          • administrator says:

            That’s a good point. Rather than research and develop – and debug – an algorithm to determine unusually dates, we can sometimes find that information in the database. Or put it in the database ourselves from some external reference.

  3. ROBERT O says:

    I am looking for help with:

    Let #Date_diff = datediff ($Eff_Dte, $Pay_End_Dt, $MONTH)
    If ‘datediff’ < 6

    I am looking to compare employee start date vs. current pay period end date, to check whether a specific earn type was used within 6 months of their start date.

    These are the errors I am getting:

    Error on line 620:
    (SQR 4045) Function or operator ‘datediff’ requires date argument.

    Error on line 620:
    (SQR 4045) Function or operator ‘datediff’ requires date argument.

    Error on line 621:
    (SQR 4048) Function or operator ‘<’ must be a string or date argument.

    Thanks, Robert.

    • administrator says:

      Welcome to my blog, Robert.

      The datediff function only works with date variables. You have to declare them in the setup section like this:

      begin-setup
      declare-variable
      date $eff_dte $pay_end_dt
      end-declare
      end-setup

      Then you need to set their values properly. If you are getting values from a database table, this is sufficient:

      let $eff_dte = &EFFDT
      let $pay_end_dt = &PAY_END_DT

      But if you get them from any other source, you have to do something like this:

      let $eff_dte = strtodate(’05/21/2009′, ‘mm/dd/yyyy’)
      let $pay_end_dt = strtodate(’30-APR-2009′, ‘dd-mon-yyyy’)

      This seems like a lot of work, but I think it’s worth it. Then your datediff will work and you can write:

      if #date_diff < 6

      I assume your “if ‘datediff’ < 6″ was a typo. You were comparing a string and a number.

  4. Kevin says:

    Hi,

    I have a question about the use of the DATENOW()function in PeopleSoft SQR. In my pursuit of answers I came across this blog. I’m not sure asking this question is appropriate, but here goes.

    I need to get the current datetime and update a field in a table. When I use the DATENOW() function I am getting the current date with time of 12:00:00 AM. Any ideas about what is occurring here

    Thanks,
    Kevin

    • administrator says:

      Welcome to the blog, Kevin. Questions are always welcome.

      If you are storing the date in a variable, like this:

      let $now = datenow()

      you need be declare the variable as a date variable, like this:

      begin-setup
      declare-variable
      date $now
      end-declare
      end-setup

      If you don’t declare $now as a date variable, SQR will make it a string variable. When you set $now equal to datenow(), SQR will convert the date to a string that looks like a date in your default format. Then, when you set a database date field to that value, SQR will convert the string to datetime that is 0 seconds after midnight.

      You may find it easier to use the built-in database variable for system date and time. In Oracle, it is SYSDATE. You could write something like this:

      begin-sql
      update tablename
      set datefield = sysdate
      where keyfield = ‘X’
      end-sql

  5. Dave Hallas says:

    Hi

    I’ve hit a problem inserting date values into SQL Server 2005 db – I’m using SQR and am reading values (Termination and Contract End Dt’s) from one table and inserting them into another (interface) table – when there is a value, I have no issue, but when the source table, and therefore the target variable, contains a NULL value, the data is inserted (or interpreted by SQL server) as 01/01/1900.

    Any Ideas on how to get around this?

    Thanks for the help!

    • administrator says:

      I haven’t worked in SQL Server, but it sounds like it stores dates as the number of days since January 1, 1900. Dates before then would be negative numbers. Null dates may be stored as zero, with some flag to distinguish them from 01/01/1900 itself. Perhaps that flag got lost in your program. Did you do the inserts with the column variable you read (&TERM_DATE) or did you transfer the value to an SQR variable ($term_date)? If it was an SQR variable, did you declare it as a date variable? Your program may have to check for “01/01/1900″ and set those dates to null.

  6. Leena says:

    I need help… I need to insert datetime to a table
    No matter what i do, it returns only date
    I tried
    1. Datenow()
    2. Accepting the date, doing to_char and that time it shows correctly, but as soon as i do strtodate, even if i give strtodate(&audit_stamp,’DD-Mon-YYYY HH24:MI:SS’), it will return just the date and not the date time.

    • administrator says:

      If you write:

      let $x = datenow()

      You should make sure that $x is a date variable, not a string variable. You do that in the setup block:

      begin-setup
      declare variable
      date $x
      end-declare
      end-setup

      Alternately, you could write:

      let $x = datetostr(datenow(), ‘mm/dd/yyyy hh:mi:ss’)

      Your note indicates that this is working properly. I assume you insert a row with the $x variable placed in a date, datetime, or string field. The insert should work with all three field types, but you would handle the fields differently when reading them back into an SQR program.

      What type of field is &AUDIT_STAMP? Since you used to_char successfully, I assume it is a date or datetime field (which is a distinction in PeopleTools, not in the Oracle database). In that case, you can use to_char, edit, or datetostr to convert it to a string. You would not use strtodate() – that is a function that starts with a string, and you are starting with a date.

      If &AUDIT_STAMP is a string, and you are storing it as a date, you may need datetostr() to format a string to output that date.

  7. John says:

    Hi,

    I need to subtract from date in the format years,months,days. For example, i want to subtract 5 years, 2 months and 3 days from current date. How can this be done in SQR?

    Thanks in advance