Comment ExcelNumberFormat for formatting date and time.

[JamesBowen]

As many of us know, ABL has long struggled with built-in support for formatting dates and times, often leaving us to craft our own formatting solutions.

While troubleshooting an unrelated issue, I came across the ExcelNumberFormat .NET assembly — a handy library for parsing ECMA-376 number format strings and formatting values in a way that's consistent with Excel.

I've put together a quick proof of concept that takes an ABL DATETIME and an Excel-style format mask, and returns a nicely formatted, human-readable string.

This approach requires the ExcelNumberFormat 1.1.0 library, which can be downloaded separately. So far, I've only tested it on Windows, but based on what I understand, it should work on Linux as well.

Anyway, help yourself.

//Change this to your local country
&SCOPED-DEFINE xLocality nz-NZ

FUNCTION formatDateTime RETURNS CHARACTER
    ( INPUT pDateTime AS DATETIME,
    INPUT pDateFormat AS CHARACTER ):
    /*------------------------------------------------------------------------------
     Purpose:
     Notes:
    ------------------------------------------------------------------------------*/ 
  
    DEFINE VARIABLE formatter   AS CLASS     ExcelNumberFormat.NumberFormat   NO-UNDO.
    DEFINE VARIABLE result      AS CHARACTER NO-UNDO.
  
    formatter = NEW ExcelNumberFormat.NumberFormat(pDateFormat).
  
    result = "Invalid Date Format".
  
    IF formatter:IsDateTimeFormat THEN
        result = formatter:Format( CAST (BOX(pDateTime), System.DateTime) , NEW System.Globalization.CultureInfo("{&xLocality}"), FALSE) .
  
    RETURN result.
  
    FINALLY:
      
        IF VALID-OBJECT(formatter) THEN
            DELETE OBJECT formatter. 
      
    END.
      
END FUNCTION.

Example:

DEFINE VARIABLE dateTimeStamp AS DATETIME NO-UNDO.

dateTimeStamp = NOW.

MESSAGE formatDateTime( dateTimeStamp, "d/mm/yyyy h:mm:ss" ).
MESSAGE formatDateTime( dateTimeStamp, "d/mm/yyyy h:mm:ss am/pm" ).
MESSAGE formatDateTime( dateTimeStamp, "d/mm/yyyy hh:mm:ss" ).
MESSAGE formatDateTime( dateTimeStamp, "d/mm/yyyy hh:mm" ).
MESSAGE formatDateTime( dateTimeStamp, "dd-mm-yyyy hh:mm" ).
MESSAGE formatDateTime( dateTimeStamp, "dd/mm/yyyy hh:mm" ).
MESSAGE formatDateTime( dateTimeStamp, "dd mmmm yyyy hh:mm" ).
MESSAGE formatDateTime( dateTimeStamp, "dddd, dd mmmm yyyy hh:mm am/pm" ).
MESSAGE formatDateTime( dateTimeStamp, "yyyy, dd mmmm hh:mm" ).