Robelle Tech: Suprtool Date Functions

September 2004

Get the most out of your Robelle Tools

Suprtool Date Functions – Part 1

By Robelle Staff

Suprtool has so many date functions that it is difficult to know where to start in presenting them. So we have decided to do several columns on dates, starting with some specific applications to show the power of Suprtool’s date functions.

Add and Subtract Dates

One common business task is to generate a date value that is N-days before or after another date value in your database. For example, FOLLOWUP-DATE might need to be a week after SHIPPED-DATE.
With the new $Days function in Suprtool, you can easily generate a date that is N-days before or after any date. You only need to use two Suprtool tasks. The input date can be in any supported format, but the output date will be in yyyymmdd format.

The first task generates the desired date, but it will be in Julianday format. For simplicity, we assume that the file only contains the date, in yyyymmdd X8 format.

>input YOURFILE >def shipped-date,1,8 >item shipped-date,date,yyyymmdd >def jdate,1,4,int >item jdate,date,julianday >ext shipped-date >ext jdate = $days(shipped-date) + 7 >out tmpfile,link >xeq
The second task converts the Julian-format date to yyyymmdd format.

>in tmpfile >def followup-date,1,8,display >item followup-date,date,yyyymmdd >ext shipped-date >ext followup-date = $stddate(jdate) >out result,link >xeq

Now you have a self-describing file with the following information:

SHIPPED FOLLOWUP 19981231 19980107 19991230 19990106 19990228 19990307

Selection Based on Day of the Week

Lynda Bechel from Meyer Distributing asked: “Is there a way to pick dates that will include only the dates between the last day of the month ($date(*/*-1/last) and the prior Monday? This is for a report that looks at only the days from a Monday to the last day of the month for partial week.”

Well, yes there is. The key to this is knowing that Julian Day 0 was a Monday. So if you convert any date to a Juliandays value (via Suprtool’s “$days” function), and divide by 7, the modulus would tell you how many days that date is from the previous Monday.

So the steps for achieving this are:

1. Get the date of the last day of the month.
2. Convert that date to a juliandays value.
3. Divide the juliandays by 7, the modulus shows how many days the date was beyond the previous Monday.
4. Deduct that number of days from the date in 1) (above), to calculate the date of the last Monday.

Here’s the Suprtool code to insert at the beginning of the jobstream:

purge dtfile purge dtfile2 purge dtfile3 input catalog.pub.sys {input any file with at least 1 record} def lastdate,1,8,display item lastdate,date,yyyymmdd ext lastdate = $date(*/*-1/last) {output the date for last day of last month} num 1 {only need 1 output record} out dtfile,link xeq in dtfile def lastmonday,1,4,int extract lastdate ext lastmonday=($days(lastdate)-($days(lastdate) mod 7)) {calculate juliandays value for the previous Monday} out dtfile2,link xeq in dtfile2 extract ‘setvar lastdayoflastmonth,’, lastdate {create “setvar” for the last day of previous month} :file dtfile3;rec=-80 out dtfile3,ascii num 2 {leave space for a second record} set squeeze off xeq in dtfile2 {create a “setvar” command for the previous Monday} item lastmonday,date,julianday extract ‘setvar previousmonday,’ ext lastmonday = $stddate(lastmonday) out dtfile3,ascii,append xeq use dtfile3
DTFILE3 now contains 2 setvar commands:

/l dtfile3 1 setvar lastdayoflastmonth,20001231 setvar previousmonday, 20001225
... and the file has been “use”d in Suprtool, so the variables have been set. They can then be referenced further down in the jobstream, as follows:

>set varsub on >if mydate >= !previousmonday and mydate <= !lastdayoflastmonth >verify if IF mydate >= 20001225 and mydate <= 20001231 >

Note that the variables will insert the actual numeric values into the IF command, so it will make for efficient data selection. We could reduce the number of passes in the above script to generate an IF command directly, as in:

if $days(mydate) <= {juliandays value of lastdayofmonth} & and $days(mydate) >= {juliandays value of previous monday}

... but this would mean that Suprtool would have to calculate the juliandays value for every record read at runtime, so it would be less efficient.

Suprtool’s $Stddate Function

Suprtool has a feature that can greatly help in date selection and conversion. $Stddate converts dates from any of the formats that Suprtool recognizes to a common standard format, ccyymmdd.

With this conversion, it becomes possible to compare two dates that are not in the same format. And because $stddate puts century and year first, you can reliably do greater-than and less-than comparisons.

$Stddate can be used in two places in Suprtool. In the IF command it is used for selecting records based on date criteria. In the Extract command it is used for converting dates to the standard ccyymmdd format.

Comparing Two Dissimilar Dates

When Suprtool compares two fields to each other, it does not try to interpret them. If they are character fields, it just compares the bytes. If they are numeric fields, it just compares the numeric values. Using $stddate forces Suprtool to convert the date fields to a common format before comparing them.

Get shipping-records Item order-date, date, mmddyy Item date-shipped, date, ddmmyyyy if $stddate(date-shipped) > $stddate(order-date)...

Converting Dates to CCYYMMDD Format

Suprtool can convert any known date formats to the standard cyymmdd format. This can be done by using $stddate in the Extract command. The date being converted must be defined as a numeric field. You need to define an eight- digit numeric “container” to receive the converted date.

get shipping-records item order-date, date, mmddyy define converted-date, 1, 8, display extract converted-date = $stddate(order-date) ...

If you are creating a self-describing (link) output file, remember to tell Suprtool that the new field is in ccyymmdd format.

item converted-date, date, ccyymmdd output myfile,link xeq
What About Invalid Dates?

Something to keep in mind is that the $stddate function can only convert dates that are valid. A valid date is one that appears on the calendar. Therefore special dates such as all zeros, blanks, nines, etc. will need special attention. Any dates that are not valid will be converted to zero by $stddate. You need to be aware of this when you design your Suprtool task. You can ensure that $stddate sees only valid dates by filtering out the invalid ones with the $invalid function.

if not $invalid(date-shipped) and & not $invalid(order-date) & and $stddate(date-shipped) > $stddate(order-date)
Apply $stddate to user input dates

To check a user-input date against $stddate, you can use the $date function on the user date.

input db;prompt=”Enter the start date yymmdd: “ input de;prompt=”Enter the end date yymmdd: “ setvar dbyy str(‘!db’, 1,2 ) setvar dbmm str(‘!db’, 3,2 ) setvar dbdd str(‘!db’, 5,2 ) setvar deyy str(‘!de’, 7,2 ) setvar demm str(‘!de’, 9,2 ) setvar dedd str(‘!de’, 11,2 ) echo if $stddate(database-date) !>= $date(!dbyy/!dbmm/!dbdd) and & $stddate(database-date) !<= $date(!deyy/!demm/!dedd) >chkdate run suprtool base mybase get mydset use chkdate ext key-value, database-date out result xeq
Set Date Cutoff

When $stddate converts a date format without a century to ccyymmdd, it needs to decide what century to add. This is determined by the Set Date Cutoff command, which defines the starting year of a 100-year date window. The default year is 10, which means that incoming yy values of 10 through 99 will have century 19 applied, and incoming yy values of 00 through 09 will have century 20 applied. If you set the cutoff value to 50, the 100-year span would go from 1950 through 2049, with century applied accordingly.