/* SortDateEncodeDecode.sql 
   2011-11-25 by ve3meo
   
An encoding algorithm that produces values identical to RM4's EventTable SortDate for single dates but has discrepancies
when dates entered in RM4 have modifiers such as Bef, Aft, From-To, etc.

The corresponding decoding algorithm correctly decodes RM4 SortDates that are single, absolute dates, not ones that were
created with date modifiers
*/

/* SortDateEncodeError */
SELECT Date, Calc-SortDate FROM
(
/* SortDateEncode */
SELECT Date, SortDate, 
       (CASE 
        WHEN Date LIKE '.%' 
        THEN 1 
        ELSE Substr(Date,3,5) END 
        +10000
        )*562949953421312 
        + Substr(Date,8,2)*35184372088832 
        + Substr(Date,10,2)*549755813888 
        + 17178820620 
        AS Calc 
FROM EventTable
)
;

/* SortDateDecode */
SELECT Date, SortDate, 
       (SortDate - 17178820620) / 562949953421312 - 10000 AS Year, 
       (SortDate - 17178820620) % 562949953421312 / 35184372088832 AS Month,  
       (SortDate - 17178820620) % 562949953421312 % 35184372088832 / 549755813888 AS Day,  
       (SortDate - 17178820620) % 562949953421312 % 35184372088832 % 549755813888 AS Remainder  
       FROM EventTable 
;