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
>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:
- Get the date of the last day of the month
- Convert that date to a juliandays value
- Divide the juliandays by 7, the modulus shows how many days the date was beyond the previous Monday.
- Deduct that number of days from the date in 1) (above), to calculate the date of the last Monday.
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 2 setvar previousmonday, 20001225 3... 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.
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 K2Some 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 > xeqCreate 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 fooThis 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. "
GET LSILM002
DEF YY,SYSTEM-DATE[1],2
DEF MM,SYSTEM-DATE[4],2
DEF DD,SYSTEM-DATE[7],2
EXT DCCODE
EXT PRODUCT
EXT RECEIPTNO
EXT SYSTEM-DATE
EXT YY
EXT MM
EXT DD
OUTPUT GRNSIP03,QUERY
X
INPUT GRNSIP03
DEF DCPRODRECPT,1,18
DEF YYMMDD,47,6
ITEM YYMMDD,DATE,YYMMDD
IF ($STDDATE(YYMMDD) >= $TODAY(-7) &
AND $STDDATE(YYMMDD) <= $TODAY)
EXT DCPRODRECPT
EXT SYSTEM-DATE
SORT DCPRODRECPT
OUTPUT GRNSIP04,QUERY
X
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:input catalog.pub.sys define dt,1,6 item dt,date,YYMMDD extract dt = $today(-7) extract dt = $today output dtfile,temp numrecs 1 xeq 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 xeqThis 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.