Date Fields in Selects (TB #112) (Functions/Subroutines/Programs)

OpenInsight and Advanced Revelation developers should be aware of 2 dates that can affect your systems in certain, critical ways. These dates are May 18, 1995 and August 26, 1995. The first error occurs on May 18, 1995 when the internal system date reaches 10,000. This can cause sorting errors due to field justification. The second error occurs when an internal date is also a valid external date. The first internal date that meets this pattern is 10100 (August 28, 1995)

Explanation of Internal Dates

All Revelation Technologies products store dates in an internal format. This formatting is triggered if you apply a date type validation pattern to the dictionary field. The internal format of a date is stored as an integer. Date 0 is December 31, 1967. Date 1 is January 1, 1968. Dates prior to December 31, 1967 are negative integers. Based on this formula, day 10,000 will be on May 18, 1995 and day 10,100 will be on August 26, 1995.

Each date is treated differently by the system. Date 10,000 can affect systems though justification errors. Date 10100 can affect systems through implied formatting errors.

A developer can also run into problems if he or she assumed that the internal date will always be 4 characters in length. Revelation Technologies has no way to determine the scope of this throughout your application. Each developer needs to independently determine whether a 5 character internal date will have repercussions in his or her application.

Justification Problem

On May 18, 1995 the internal date will reach 10,000. Applications can be affected by this change in various ways. The most common effect this change will have occurs with sorting. Revelation Technologies provides two sort orders during sorting, left (alphabetic) and right (numeric) justification. All numeric fields should have right justification for proper sorting.

If your date fields are left justified, your sorts will be incorrect. Date fields must be right justified to maintain proper sorting order. If you have indexes set to left justification on any date field, you should rebuild these indexes.

Implied Formatting Problems

Implicit date formatting is a feature added in Advanced Revelation for data entry purposes. Date values can be entered without separators to speed data entry.

For example, the value 113095 will be treated the same as 11/30/95 or 11.30.95 which is November 30, 1995. Alternatively, 81766 will be treated as 8/17/66 or 08/17/66 or August 17, 1966.

The system will prefix a 0 to 5 character number, then check for a valid formatted date, including implied separators. The first time an internal date is also an external date is 10100, August 26, 1995. All dates until February 19, 2004 have internal dates that can be reconverted into internal dates. The next set of date ranges after that are January 11, 2023 through September 11, 2030. (Internal values 20100 - 22900).

How this effects SELECTS and FILTERS

All select statements and filters (SELECT and R/LIST WITH clauses and BTREE.EXTRACT criteria statements) expect all data passed to them to be in external format.

If you have a dictionary item ORDER_DATE in the file ORDERS_FILE, and you would like to find all orders placed in the month of January 1995 you should enter

SELECT ORDERS_FILE WITH ORDER_DATE

FROM 01/01/95 TO 01/31/95

Many developers use this standard when entering data from the command line. However, many developers use the internal format (from programs) when prompting users for information. A standard way of gathering information on date ranges is to present a collector window to the user. Sample code follows:

………

catalyst( W , FROM_TO_DATES )

/* assume valid data from collector for this example */

startDate = @pseudo<1>

endDate = @pseudo<2>

statement = SELECT ORDERS_FILE

WITH ORDER_DATE FROM :

startDate : TO : endDate

perform statement

………

This would result in a TCL statement that looked like

SELECT ORDERS_FILE WITH ORDER_DATE

FROM 9863 TO 9893

The system will bring back the correct results of this query. OpenInsight and Advanced Revelation are designed so that during a query, should the query value supplied fail the conversion, the query will use the explicit value passed. Without this option, the system would use a null value. This option was put in place to allow for unstructured systems and lack of strong data typing in the system.

This implementation can now cause problems for developers that have come to rely on using internal date formats during selects. If we change the above select statement to find all orders in the month of September 1995, we will end up with a select statement that looks like:

SELECT ORDERS_FILE WITH ORDER_DATE

FROM 10106 TO 10135.

The input conversion on date will take these numbers, and apply input conversions, using the implied date structure. 10106 is treated as 010106 which is January 1, 2006. 10135 is treated as 010135 which is treated January 1, 1935. The input values for these are 13881 for January 19, 2006 and -12052 for January 1, 1935.

The system will run a select trying to find all dates greater than or equal to 13881 and less than or equal to -12052. Since this is a mathematical impossibility in the realm of real integers, this select will fail.

Another date that can cause problems with internal validation is June 6, 1940. This date has an internal date of -10100. The input conversion routines will strip off the minus sign and treat the number as 10100 or January 1, 2000 which is internal date 11689.

Solutions Available to Developers

Developers who run into these problems have a few solutions.

The only solution for the date 10,000 problem is to redefine your date fields so they are right justified.

The implied date formatting problem presents the developers with an interesting conundrum. Revelation Technologies has made available a replacement input conversion routine ($RTP26) that will work on all versions of OpenInsight and Advanced Revelation. This replacement conversion routine will not validate implied formatting without an explicit 0 preceding the value. This means the next date affected by this problem will not occur until internal date 110100, June 10, 2269.

There will be an effect on end-users of your products by incorporating this fix. The fix is global across the entire system. Data entry windows will no longer allow the keypunch operator to enter and rely on the implied formatting. If a keypunch operator relies on this feature, he or she will have to change their pattern of data entry. It could also affect automated processes, capture scripts or other processes relying on the current syntax. This problem is not one that can be solved without some effort by both the developer and the end-users of a system.

  • kb/kb_articles/kb0042.txt
  • Last modified: 2024/01/30 13:36
  • by 127.0.0.1