Robelle Tech: MPE Quickstart for Unix Users

October 2004

Get the most out of your Robelle Tools

Suprtool Date
Functions – Part 2

By Robelle Staff

Last month in Part 1 of “Suprtool Date Functions” we showed how to add and subtract dates, do selection based on day of the week, and many more useful tasks.

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

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

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

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.

Suprtool Date Functions – Part 2