Problem with sorting dates in Envision

I have encountered a bug today with one of the sort subroutines, S.SORT.BY.DL, that could result to other sorting issues. In one of the processes, the dates were not sorted in the correct descending-order. After some much digging through log files and test cases, I finally shred some lights to the issue. If the date array to be sorted contains date that is before 5/18/1995, the array will not be sorted correctly. The reason for this bug is related to the way Unidata stores date in the database. Unidata dates are stored as a counter from a default date, 1/1/1968. All dates are presented as a numbers, so 1/2/1968 has a value of 2, and 1/5/1968 has a value of 5 and so on. 

For that reason, let's consider this example. Say we have an array of dates of 01/01/2000 and 5/17/1995. In envision these two dates are presented by 11689 and 9999. Because Unibasic(Envision) is a loosely-typed language, these numbers can be considered as "strings" at run-time when their type is not strictly/explicitly enforced. So after sorting the date array with descending order:

XL.DATES = 11689:@VM:9999

You will get this:

XL.DATES = 9999:@VM:11689

This is because 9 is greater than 1, so 9999, as a string, is greater than string 11689. This is incorrect because 11689 is greater than 9999, as well as 01/01/2000 is later than 5/17/1995. I also tested S.SORT.ASSOC.DL and it seems to behave the same way. 

This issue is more applicable when you work with historical data. It can happen again in 80k days when the date reach 100,000. That is more than 200 years from now, I wonder if Unidata still exists, but I am sure I won't be around to worry about it.

Check to see if a date is already converted to internal format

IF NOT(NUM(A.DATE)) THEN

      * date is not in internal format, needs converting

      A.DATE = ICONV(A.DATE,X.SN.DATE2)

END

Useful for input validation of dates on things like subroutines and ELF

* from Trevyn Bowden.

Outputting date using OCONV

X.TEXT = OCONV(DATE(), "D") 09 Oct 2013
X.TEXT = OCONV(DATE(), "DDMY") 09 10 2013
X.TEXT = OCONV(DATE(), "DMDYA") October 09 2013
X.TEXT = OCONV(DATE(), "DMDYA3") Oct 09 2013
X.TEXT = OCONV(DATE(), "D4/") 10/09/2013
X.TEXT = OCONV(DATE(), "DWA") Wednesday
X.TEXT = OCONV(DATE(), "DD") 09 
X.TEXT = OCONV(DATE(), "DM") 10
X.TEXT = OCONV(DATE(), "DW") 3 (this is quarter)
X.TEXT = OCONV(DATE(), "DDMY,A,Z4") 09 October 2013