The rest of this FAQ discusses these Suprtool topics:
In Suprtool 4.0, we introduced some new command-parsing features that let you control how Suprtool parses the year component in the $date function. You can either use two-digit years by applying a cutoff rule or you can force all years into a four-digit format.
When used with four-digit years data values to two-digit $date values, Date Cutoff tells Suprtool what century to use in the constant date value of the $date function. This setting only affects the date values generated by the $date function in the If and Extract commands.
Before version 4.0, Suprtool assumed a value of 19 for the century of a user-specified $date with a two-digit year. For example:
>item date-field,date,ccyymmdd
>if date-field <= $date(40/12/26)
The $date function in earlier versions of Suprtool would have converted
the user-specified $date to 1940/12/26 for comparison to the
date field format of CCYYMMDD. With Set Date Cutoff xx,
Suprtool now assumes a value of 20 for the century if the two-digit
year specified in the $date function is less than or equal to the
value of Set Date Cutoff. For example:
>set date cutoff 50
>item date-field,date,ccyymmdd
>if date-field <= $date(40/12/26)
In this case Suprtool assumes the full $date to be 2040/12/26.
Conversely, if the value of Set Date Cutoff is 40 or less, then the
assumed full $date would be 1940/12/26. The default value of Set Date Cutoff is 10.
We recommend always providing a four-digit year when using the $date function. For reasons of backward compatibility, however, we introduced the Set Date Cutoff command. See Set Date ForceCentury for more information.
When used with four-digit years, Set Date ForceCentury On does not allow a YY date to be entered in the $date function; it forces the user to enter a full CCYY date.
>set date forcecentury on
>item date-field,date,ccyymmdd
>if date-field >= $date(96/12/10)
Error: You must specify the century or Set Date ForceCentury off
The default value for Set Date ForceCentury is Off.
If you do not include the century in your dates (the second solution above), you will have the following problems:
Currently, the date format of YYMMDD collates (sorts) correctly if the date is not after 1999. If the current date is December 10, 1996 for example, its numerical value (961210) is less than the value for December 10, 1997 (971210).
At the turn of the century, dates in the YYMMDD format (or YYMM) will no longer sort correctly because the numeric values of dates in the 21st century will be sorted before date values in the 20th century. For example, the numeric value of 001210 (December 10, 2000) will come before 961210 (December 10, 1996) in the sort sequence. Consequently, if a date after 1999 is stored in a YYMMDD format, a relative operation such as
>if date-field >= $date(96/12/10)
will not select the date of December 10, 2000. You will need to use the
$stddate function
to make this task work correctly.
>if $stddate(date-field) >= $date(96/12/10)
will select the date of December 10, 2000.
> item invoice-date,date,YYMMDD
> if invoice-date < $today
is exactly the same as
> if invoice-date < 980401 {on 1 April 1998}
Suprtool selects records on the numeric value of the
field, not on the implied date value. If we move the calendar ahead to January 1,
2000 and repeat the commands above, the result would be the same as
if
> if invoice-date < 000101 {on 1 January 2000}
had been typed.
If there were some invoice dates from the previous century (e.g., 990101
for December 1, 1999), they would not have been selected.
When used with two-digit years, Date Cutoff tells Suprtool what century to use when the $stddate function is converting your two-digit years to the standard CCYYMMDD format. The setting tells which year starts getting "19" as the century, all years lower than this setting get "20" as the century. e.g.
> set date cutoff 30
> item invoice-date,date,yymmdd
> if $stddate(invoice-date) <= $date(*+4/*/*)
In this case all invoice dates with a yy value between 30 and 99 will have 19 as the century. All invoice dates with a yy value of less than 30 will have 20 as the century generated by the $stddate function.
Sometimes. Because dates after 1999 do not collate properly for the YYMMDD and YYMM formats, the If command in version 4.0.11 and later will produce an error when the following set of conditions exist:
>item enddate, date, yymmdd
>if enddate >= $date(*+4/*/*) {21st century date}
^
Error: Cannot use a date beyond 1999 for this format
Although this is the
default operation in Suprtool 4.1, it can be overridden with the
following new Set command:
>set date ifyy2000error off
This command tells Suprtool to allow the previously described relative operations
and avoid the error message.
While you can override the
error check, the behavior of $today and $date has not changed.
The recommended solution is to used the new $stddate function to convert the two-digit year into a four-digit year.
>if $stddate(enddate) >= $date(*+4/*/*)
> item invoice-date,date,CCYYMMDD
See the sections How Do I Convert a J2 Date from YYMMDD to CCYYMMDD? and How Do I Convert an X6 YYMMDD Date to an X8 CCYYMMDD Date? for more details on converting two-digit years into a four-digit format.
The $stddate function can convert six-digit date formats to CCYYMMDD. But what if all the dates are not actually dates, but some dates are filled with 9s as a flag to an application?
Consider this dataset with two date fields, J2 items and in the date format YYMMDD.
Database: STORE.DB.GREEN
D-SALES Detail Set 5
Entry: Offset
CUST-ACCOUNT Z8 1 (!M-CUSTOMER)
DELIV-DATE J2 9
PRODUCT-NO Z8 13 (M-PRODUCT)
PRODUCT-PRICE J2 21
PURCH-DATE J2 25
SALES-QTY J1 29
SALES-TAX J2 31
SALES-TOTAL J2 35
Capacity: 602 (14) Entries: 10 Highwater: 10 Bytes: 38
First, we need to know and understand our data. Are there any invalid dates? If
so, does the value have some other logical meaning?
>get d-sales
>item deliv-date,date,yymmdd
>item purch-date,date,yymmdd
>if $invalid(deliv-date) or $invalid(purch-date)
>list
>xeq
>GET D-SALES (9) >OUT $NULL (0)
CUST-ACCOUNT = 10010 DELIV-DATE = 999999
PRODUCT-NO = 50513001 PRODUCT-PRICE = 19220
PURCH-DATE = 999999 SALES-QTY = 2
SALES-TAX = 2691 SALES-TOTAL = 21910
>GET D-SALES (10) >OUT $NULL (1)
CUST-ACCOUNT = 10010 DELIV-DATE = 125213
PRODUCT-NO = 50513001 PRODUCT-PRICE = 19220
PURCH-DATE = 1 SALES-QTY = 2691
SALES-TAX = 21910 SALES-TOTAL = 21910
IN=10, OUT=2. CPU-Sec=1. Wall-Sec=1.
In this example,
we see two records that do not contain proper dates. The first record
contains all 9s, which is probably used as some sort of flag. We may need
to add 99 in front of these dates.
But the second record is obviously wrong. We can use Dbedit to correct this record
before converting the other dates. We need to know our data
to properly convert to a new date format.
Once all the incorrect dates are fixed, we can start converting. We can add a prefix of 19 or 20 to all the appropriate dates by using the following Extract statement. Please note that we are updating this directly. In case we need to redo this task, we only convert those dates that have not yet been converted. In this example we set the cutoff year to 30 so any dates before 30 will have '20' as the century and the others will have '19'.
>get d-sales
>set date cutoff 30
>item purch-date,date,yymmdd
>item deliv-date,date,yymmdd
>if not $invalid(purch-date) and not $invalid(deliv-date)
>update
>ext purch-date = $stddate(purch-date)
>ext deliv-date = $stddate(deliv-date)
>xeq
We have now converted all the J2 YYMMDD dates to a CCYYMMDD format
and added the correct century to the date.
Database: STORE.DBOLD.ACCOUNT
D-SALES Detail Set 5
Entry: Offset
CUST-ACCOUNT Z8 1 (!M-CUSTOMER)
DELIV-DATE X6 9
PRODUCT-NO Z8 15 (M-PRODUCT)
PRODUCT-PRICE J2 23
PURCH-DATE X6 27
SALES-QTY J1 33
SALES-TAX J2 35
SALES-TOTAL J2 39
Capacity: 611 (13) Entries: 15 Highwater: 15 Bytes: 42
We want to convert to a date format that has room for the century (CC)
at the beginning of deliv-date and purch-date:
Database: STORE.DB.ACCOUNT
D-SALES Detail Set 5
Entry: Offset
CUST-ACCOUNT Z8 1 (!M-CUSTOMER)
DELIV-DATE X8 9
PRODUCT-NO Z8 17 (M-PRODUCT)
PRODUCT-PRICE J2 25
PURCH-DATE X8 29
SALES-QTY J1 37
SALES-TAX J2 39
SALES-TOTAL J2 43
Capacity: 608 (16) Entries: 0 Highwater: 0 Bytes: 46
To convert these dates, we need to put either a 19 or 20
in front of the YYMMDD date, depending on the value of the year. Before we
can proceed, however, we must once again confirm that there are no invalid
dates.
>base store.dbold
>get d-sales
>item deliv-date,date,yymmdd
>item purch-date,date,yymmdd
>if $invalid(deliv-date) or $invalid(purch-date)
>list
>xeq
Once we have fixed the invalid dates, we can start
converting. Because there are two date fields in this
dateset, we must be careful to add the appropriate century to the correct
field. In this example, we assume that years before 1950 are in the 20th century.
We can easily convert each date using the new $stddate function but it is important to note that you will need you redefine the date fields as numeric (display).
>base store.dbold,1
Database password [;]?
>get d-sales
>set date cutoff 50
>define deliv-date-z,deliv-date,display
>define purch-date-z,purch-date,display
>item deliv-date-z,date,yymmdd
>item purch-date-z,date,yymmdd
>ext cust-account
>ext $stddate(deliv-date-z)
>ext product-no, product-price
>ext $stddate(purch-date-z)
>ext sales-qty / sales-total
>put d-sales,store.db,1
Database password [;]?
>xeq
This converts two dates from an X6 to an X8 format.
The following Suprtool task shows how we convert a date in a self-describing file from a MMDDYY to a YYMMDD format. Consider the following self-describing file with the deliv-date and purch-date fields:
File: SALES04.DATA.ACCOUNT (SD Version B.00.00)
Entry: Offset
CUST-ACCOUNT Z8 1
DELIV-DATE X6 9 <<MMDDYY>>
PRODUCT-NO Z8 15
PRODUCT-PRICE I2 23
PURCH-DATE X6 27 <<MMDDYY>>
SALES-QTY I1 33
SALES-TAX I2 35
SALES-TOTAL I2 39
Limit: 115 EOF: 15 Entry Length: 42 Blocking: 97
We want to convert these two dates to a data format of YYMMDD before
adding a century in front of the year. This can be accomplished easily
by defining each component in the date and extracting the components in
the new order.
>in sales04
>def deliv-date-mm,deliv-date[1],2
>def deliv-date-dd,deliv-date[3],2
>def deliv-date-yy,deliv-date[5],2
>def purch-date-mm,purch-date[1],2
>def purch-date-dd,purch-date[3],2
>def purch-date-yy,purch-date[5],2
>ext cust-account
>ext deliv-date-yy
>ext deliv-date-mm
>ext deliv-date-dd
>ext product-no / product-price
>ext purch-date-yy
>ext purch-date-mm
>ext purch-date-dd
>ext sales-qty / sales-total
>out sales05,link
>xeq
We now have a file with the dates in YYMMDD order, but the self-describing
information shows three separate fields:
File: SALES05.DATE.ACCOUNT (SD Version B.00.00)
Entry: Offset
CUST-ACCOUNT Z8 1
DELIV-DATE-YY X2 9
DELIV-DATE-MM X2 11
DELIV-DATE-DD X2 13
PRODUCT-NO Z8 15
PRODUCT-PRICE I2 23
PURCH-DATE-YY X2 27
PURCH-DATE-MM X2 29
PURCH-DATE-DD X2 31
SALES-QTY I1 33
SALES-TAX I2 35
SALES-TOTAL I2 39
Limit: 115 EOF: 15 Entry Length: 42 Blocking: 97
We can combine these fields into one field with another Extract
task:
>in sales05
>def deliv-date,9,6,byte
>def purch-date,27,6,byte
>item deliv-date,date,yymmdd
>item purch-date,date,yymmdd
>ext cust-account
>ext deliv-date
>ext product-no
>ext product-price
>ext purch-date
>ext sales-qty / sales-total
>out sales06,link
>xeq
IN=15, OUT=15. CPU-Sec=1. Wall-Sec=1.
The result is a file that looks like this:
File: SALES06.DATA.ACCOUNT (SD Version B.00.00)
Entry: Offset
CUST-ACCOUNT Z8 1
DELIV-DATE X6 9 <<YYMMDD>>
PRODUCT-NO Z8 15
PRODUCT-PRICE I2 23
PURCH-DATE X6 27 <<YYMMDD>>
SALES-QTY I1 33
SALES-TAX I2 35
SALES-TOTAL I2 39
Limit: 115 EOF: 15 Entry Length: 42 Blocking: 97
Now we can add the century to these fields as described above.