All About Dates

Suprtool has so many date functions that it is difficult to know where to start in presenting them. So we have decided to begin with some specific applications that 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
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
Now you have a self-describing file with the following information

     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 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

   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

   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

   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

   use dtfile3
DTFILE3 now contains 2 setvar commands:
/l dtfile3
1	setvar lastdayoflastmonth,20001231
2	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

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

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.

Define Date Fields

Because IMAGE doesn't provide a built-in date type, most databases store dates as numeric or character fields. Without help, Suprtool cannot treat these as date values.

Before Suprtool can use a date field, it has to know the format of a particular date field. Use the Item command to specify the date format. For example, to tell Suprtool that the item purch-date is a date field with a format of yyyymmdd (e.g., 20010319), you would use:

item   purch-date, date, yyyymmdd   {date format}
The formats supported are wide and varying. Suprtool is able to process virtually all date formats that appear in IMAGE databases. For dates, the date format must be one of the following, combined with a field of a compatible data type:
ASK            	J1 and K1
Calendar       J1 and K1
ddmmyy     	X6, Z6, J2, K2, and P8 or greater
ddmmyyyy  	X8, Z8, J2, K2, and P10 or greater
mmddyy       	X6, Z6, J2, K2, and P8 or greater
mmddyyyy  	X8, Z8, J2, K2, and P10 or greater
Oracle       	X7
PHdate       	J1, K1, J2, and K2
yymm        	X4, Z4, J1, and K1
yymmdd     	X6, Z6, J2, K2, and P8 or greater
yyymmdd    	J2, P8
yyyymmdd  	X8, Z8, J2, K2, and P10 or greater
ccyymmdd   	X8, Z8, J2, K2, and P10 or greater
ccyymm      	X6, Z6, J2, K2, and P8 or greater
yyyymm     	X6, Z6, J2, K2, and P8 or greater
aammdd     	X6
aamm        	X4
mmddaa     	X6
ddmmaa     	X6
ccyy         	X4, Z4, J1, and K1
SRNChronos	X6
mmyyyy      	X6, Z6, J2, K2, and P8 or greater
yyddd        	X5, Z5, J2, K2, and P8 or greater
ccyyddd      	X7, Z7, J2, K2, and P10 or greater
HPCalendar  	J2, K2
EDSDate     	J2, P8
JulianDay   	J2
PHdate8      	J1, K1, J2, and K2
Some of these are quite odd formats used by a single specific application. Check the Suprtool manual for complete definitions of each type. Note: if you are dealing with raw data from a disk file, use the Define command first to tell Suprtool the names, sizes and data types of the fields you need to select, sort or extract. In the following date examples, we show the Item command in each example. In practice, however, you only need to use the Item command once per date field, not once per task.

Select by Today's Date

Using the $today function, select the sales records whose purchase date is today.
>get    d-sales
>item   purch-date,date,yyyymmdd
>if     purch-date = $today   {select today's date}
>output result
Other tricks with $today
>if     purch-date = $today(-1)        {yesterday}
>if     purch-date = $today(+1)        {tomorrow}

Select by Particular Date

To specify a particular date, use the $date function in the If command. This example selects all the sales transactions for August 12, 2000.
>get    d-sales
>item   purch-date,date,yyyymmdd
>if     purch-date = $date(2000/08/12)
>output result

Select by Year

Suppose we want to select all the sales transactions for 2004: use a date range from January 1st to December 31st.
>get    d-sales
>item   purch-date,date,yyyymmdd
>if     purch-date >= $date(2004/01/01) and &
        purch-date <= $date(2004/12/31)
>output result

More Uses of $Stddate

$Stddate treats all dates the same way, regardless of the date format. $stddate is available to the If and Extract commands. It internally converts any date format in nearly any data-type container to the ccyymmdd format in a double integer container. That makes it possible to do many useful task:

Compare between dates of dissimilar formats

> get invoice-detail
> set date cutoff 30
> item invoice-date,date,yymmdd
> item close-date,date,mmddyyyy
> if $stddate(close-date) <= $stddate(invoice-date)
> out badinvs,link
> xeq
Create extracts with dates in a CCYYMMDD format from any internal format
> get invoice-detail
> item  invoice-date,date,hpcalendar
> define new-date-8,1,8,display   {temp num field}
> extract  new-date-8 = $stddate(invoice-date)
> extract  first-field / last-field
In this case we define a new numeric data field, new-date-8, to hold the converted invoice-date.

Compare any date field to system date variables

> get invoice-detail
> item  invoice-date,date,phdate
> if $stddate(invoice-date) >= $date(*+2/*/*)
              {two years from now}

Do less-than and greater-than comparisons on non-collating dates

> get invoice-detail
> item close-date,date,mmddyyyy
> if $stddate(close-date) < $today

Sorting on non-collating dates, using multiple passes.

Note that $stddate is not available in the Sort command, so you sometimes need two (or more) passes.
> get invoice-detail
> item close-date,date,mmddyy
> extract first-field / last-field
> define new-field,1,8,integer
> extract new-field = $stddate(close-date)
> output foo,temp,link
> xeq
> input foo
> sort new-field
> extract first-field / last-field
> output myfile
> xeq

Uses for Date Constants

In the examples above you have seen the $date function to generate data constants. Using this function you can create job streams that don't rely on hard-coded dates. The year, month and day can be a specific number (e.g., 2004) or an asterisk "*" for the current y/m/d, or a calculation (*-1 means previous m/d/y):
>if field=$date(2000/01/01) {January 1, 2000}
>if field=$date(2000-1/01/01) {January 1, 1999}
>if field=$date(*-1/01/01) {January 1, last year}
>if field=$date(*/*/01) {start of cur year and month}
>if field=$date(*/*-18/*) {exactly eighteen months ago}
>if field=$date(2001/01/first) {January 1, 2001}
>if field=$date(*/*-1/last) {last day of previous month}
Combining these features makes it possible to generate batch jobs that require no operator input. For example, to select all of the transactions for last month you would use:
>item trans-date,date,phdate
>if trans-date >= $date(*/*-1/first) and &
trans-date <= $date(*/*-1/last)

Days Function

Suprtool also has a $days function, which converts any supported date to a Julian Day number (the number of days since 4713 BC). This allows for Date arithmetic, in which you can calculate the difference between two dates, even if they have dissimilar formats. For example you could find all orders that were not shipped within 30 days of being ordered.
>if $days(SHIP-DATE) - $days(ORDER-DATE) >=30

What About the Extract Command?

You can also use $Today and $date in the Extract command to set a field to a date value, then either output it or use the Update command to update it.

Inserting a Timestamp into the Output File

There's no built-in Suprtool function for inserting the current time. However, you can use HP variables and command I/O redirection.
> define timestamp,1,8
> echo extract timestamp = "!HPTIMEF" > foo
> use foo
This will insert an X8 field called "timestamp" into each output record. The timestamp contains the time the data was extracted.

Selecting on an Unsupported Date Format

Here is an interesting Technical Support question we received on Suprtool:

"I am having a field SYSTEM-DATE declared as X(8), which is holding the date in "YY/MM/DD" format (including the "/"s) . I need to code a script to extract data, based on date. Everyday my job has to run to extract data, from today to 7 days before based on the field SYSTEM-DATE. Below is my code. Please let me know, is there any optimized coding for this spec. "


Our Reply:

Your code requires that the entire dataset is first copied to a flat file, rearranging the date to a format that Suprtool supports. If you have a large number of records, then the additional pass will be expensive, performance-wise. Unfortunately, I know of no way to remove the "/" slashes from the date field without a separate pass, as you have done. But you could improve this process by treating it as a regular character field, as the logical date values would collate correctly. You then just need to generate the comparison values in a dynamic way in the jobstream. This can be be done by constructing the "if" command in two short pre-passes, like this:
   define dt,1,6
   item dt,date,YYMMDD
   extract dt = $today(-7)
   extract dt = $today
   output dtfile,temp
   numrecs 1

   input dtfile
   define fromdateyy,1,2
   define fromdatemm,3,2
   define fromdatedd,5,2
   define todateyy,7,2
   define todatemm,9,2
   define todatedd,11,2
   extract "if SYSTEM-DATE >= '"
   extract fromdateyy,"/",fromdatemm,"/",fromdatedd,"'"
   extract " and SYSTEM-DATE <= '"
   extract todateyy,"/",todatemm,"/",todatedd,"'"
   output ifcmd
This creates a file called IFCMD that looks like this:
:print ifcmd
if SYSTEM-DATE >= '01/06/12' and SYSTEM-DATE <= '01/06/19' 
You can reference this in your main Suprtool task, just "use ifcmd". This will mean 2 (small) "pre-passes" with 1 record each, rather than 1 "pre-pass" with all the data records, so should run much faster.