SUBJECT: Robelle Software and 'Year 2000' We rebooted our test cpu with a date of Dec 31, 1999 and watched it roll over into the next millennium. We then subjected our software to rigorous regression testing. The whole test suite ran without any failures. Qedit, our text editor should have no problems with dates beyond Dec 31, 1999. "Set X" can already tag Cobol source files with 8 character dates and the list command prints the century as part of the time stamp on the heading line. Xpress, our email package, supports full Year-2000 compliant dates, starting with version 3.1.06. Suprtool, our database handyman, has supported date types with centuries since 1987, starting in version 2.9. If you are storing your dates with 4 digit years, you should have no problems. Two-Digit Years Currently the date format of YYMMDD collates (sorts) correctly if the date is not beyond December 31, 1999. Given the current date is 961210, numerically this is less than next year whose date value is 971210. At the turn of the century dates in the yymmdd format (or yymm) will no longer sort correctly because the value of December 10, 2000 (001210) is less than 961210. Consequently, if we have a date beyond 1999, stored in YYMMDD format, a relative operation such as >if date-field >= $date(96/12/10) will not find the date of December 10, 2000. If Command and Year 2000 Because dates beyond 1999 will not collate properly for the YYMMDD format, starting in version 4.0, the If command will produce an error if the year specified in a $date or $today function is greater than 1999 and the date format is YYMMDD or YYMM, and we are performing a relative operation, e.x., greater than. Set Date Cutoff Before version 4.0 Suprtool would assume 19 for the century for any user-specified $date with a two-digit year. For example: >item date-field,date,ccyymmdd >if date-field <= $date(09/12/26) Previously the $date function would convert the user specified $date to 1909/12/26 in order for it to be compared to the date-field format of CCYYMMDD. Now with Set Date Cutoff xx, we assume 20 for the century if the year specified in the $date function is less than the value of Set Date Cutoff. For example: >set date cutoff 10 >item date-field,date,ccyymmdd >if date-field <= $date(09/12/26) Suprtool in this case assumes the full $date to be: 2009/12/26 Conversely if the value of Set Date Cutoff was 5, then the assumed century would be 19. The default value of Set Date Cutoff is 10. Set Date ForceCentury Set Date ForceCentury On will not allow a yy date to be entered in the $date function, it will force 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. AAMMDD Date Format The AAMMDD date format was developed by James Overman of HP for use in their MM3000 Product. This format is only available for X6 data-type. AAMMDD is similar to YYMMDD, but the year portion of the date uses a combination of letters and numbers of the alphabet to represent years beyond 1999. By substituting a letter in the alphabet in the first position of the year, we can extend a six-digit date and also insure that the dates collate correctly. For example: YY of AAMMDD CCYY A0 - A9 2000 - 2009 B0 - B9 2010 - 2019 C0 - C9 2020 - 2029 Since letters are greater than numbers in the collating sequence we can insure that AAMMDD dates beyond 1999 will order correctly. Invalid Dates The If command now has a $Invalid function to find all invalid dates for a particular field. An invalid date is any number of a particular date format whose date equivalent cannot be found on the calendar. For example a date with a month of 99, will be considered invalid. >base store.demo Database password [;]? >get d-sales >item deliv-date,date,ccyymmdd >if $invalid(deliv-date) >out baddates,link >xeq Converting Dates Suprtool is capable of converting dates from one format to another using a variety of Suprtool features. We will show how Suprtool can convert common dates without the century to those that have the century included. While Suprtool can convert your data, it is up to you to change your programs. Adager a third-party program for changing Image database structures has the ability to change date fields. Suprtool can convert data in Image databases, flat files, self-describing files and KSAM files. Case #1 - Converting a J2 date from YYMMDD to CCYYMMDD. Since Suprtool can do arithmetic expressions we can alter a date to put 19 in front of it. But what if all the dates are not actually dates but rather filled with 9's as some sort of flag to your application. We have a 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 their 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. We see two records that do not contain proper dates, the first record contains all 9's, which is probably used as some sort of flag. You may need to add an additional leading "99" to these dates. But the second record is obviously wrong. Use DBEdit to correct this record and continue on to convert the other dates. Knowing your data is necessary in order to properly convert to a new date format. After fixing up the dates that are incorrect we can now get ready to start converting the dates. You can convert prefix 19 in front of all of the appropriate dates by using the following extract statement. Please note that we are updating this directly. So that we can redo this task if it is stopped halfway, we only convert those dates that have not yet been converted. >get d-sales >item purch-date,date,yymmdd >if not $invalid(purch-date) and purch-date < 999999 >update >ext purch-date = purch-date + 19000000 >xeq If you have records containing years between 00 and 10 and they actually represent 2000 and 2010, you should change the If command to update them in a separate pass. The first pass updates all 20th century dates; >get d-sales >item purch-date,date,yymmdd >if not $invalid(purch-date) and purch-date / 10000 > 10 >update >ext purch-date = purch-date + 19000000 >xeq Second pass for those dates in the 21-st century: >get d-sales >item purch-date,date,yymmdd >if not $invalid(purch-date) and purch-date / 10000 <= 10 >update >ext purch-date = purch-date + 20000000 >xeq We now have converted all the J2 YYMMDD dates to CCYYMMDD format and added the correct century to the date. Case #2 X6 YYMMDD data to X8 CCYYMMDD The following Suprtool task shows how we can generate a new file to put into a new database with dates in a different format. Consider the Deliv-date and purch-date fields of the D-Sales dataset: 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 with room for a CC at the beginning of the 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 In order to convert these dates we need to be able to put either a 19 or 20 in front of the YYMMDD date, depending on the value of the year. Before we can do either of these we must confirm, once again that we have 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 confirmed that there are no invalid dates we can start converting the dates that we have. Since there are two date fields in this dateset we must be careful to add the appropriate Century for the proper field. For this example we assume that if a year is less that 1950 then the century should be 20. We can easily convert each date by processing each field separately by using an intermediate self-describing file: >base store.dbold,1 Database password [;]? >get d-sales >set squeeze off >item deliv-date,date,yymmdd >if deliv-date >= $date(1950/01/01) >out sales01,link >ext cust-account >ext "19" >ext deliv-date / sales-total >xeq IN=15, OUT=14. CPU-Sec=1. Wall-Sec=5. Now insert 20 to the century for the appropriate records: >base store.dbold >get d-sales >if deliv-date < $date(1950/01/01) >ext cust-account >ext "20" >ext deliv-date / sales-total >out sales01,link,append >xeq Now we can convert the other field from the flat file, (sales01) and add a century to the purch-date field: >reset >base {close the open database} >in sales01 >item purch-date,date,yymmdd >if purch-date >= $date(1950/01/01) >set squeeze off >out sales02,link >ext cust-account / product-price >ext "19" >ext purch-date / sales-total >xeq IN=15, OUT=15. CPU-Sec=1. Wall-Sec=1. Since we extracted all 15 records we know we do not have any records with the purch-date field that need to be updated with a "20". Now we can insert the records into the new database: >base store.db >in sales02 >put d-sales >xeq Now we have converted two dates from X6 format to an X8 format. Case #3 Different date formats X6 MMDDYY data to X6 YYMMDD The following Suprtool task shows how we convert a date in a self-describing file from MMDDYY to 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 <> PRODUCT-NO Z8 15 PRODUCT-PRICE I2 23 PURCH-DATE X6 27 <> 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, prior to converting adding a century in front of the year. This can be easily accomplished by defining each sub part of the date and extracting them 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 convert these several fields to 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. We now end up with 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 <> PRODUCT-NO Z8 15 PRODUCT-PRICE I2 23 PURCH-DATE X6 27 <> SALES-QTY I1 33 SALES-TAX I2 35 SALES-TOTAL I2 39 Limit: 115 EOF: 15 Entry Length: 42 Blocking: 97 You then add the century to these fields as described above. ========================================================================