How to intelligently format datetime in SlickGrid?

Question

Question: I'm changing a ASP.NET gridview to SlickGrid.
So far, it works fine, I'm just having a tiny bit of trouble with date formatting.


My JSON-serialized test-data looks like this:

[{
        "title" : "Task 1",        
        "duration" : "5 days",        
        "percentComplete" : 47,        
        "start" : "\/Date(1230764400000)\/",        
        "finish" : "\/Date(1241128800000)\/",        
        "effortDriven" : false
    },
    {
        "title" : "Task 2",        
        "duration" : "5 days",        
        "percentComplete" : 41,        
        "start" : "\/Date(1230764400000)\/",        
        "finish" : "\/Date(1241128800000)\/",        
        "effortDriven" : false
    },
    {
        "title" : "Task 3",        
        "duration" : "5 days",        
        "percentComplete" : 42,        
        "start" : "\/Date(1230764400000)\/",        
        "finish" : "\/Date(1241128800000)\/",        
        "effortDriven" : true
    },



    {
        "title" : "Task 100",        
        "duration" : "5 days",        
        "percentComplete" : 63,        
        "start" : "\/Date(1230764400000)\/",        
        "finish" : "\/Date(1241128800000)\/",        
        "effortDriven" : false
    }]

This is how I load the data with AJAX

<script type="text/javascript" language="javascript">
    Date.prototype.getTicksUTC = function () {
        return Date.parse(this.toUTCString()) + this.getUTCMilliseconds();
    } // End Function getTicksUTC

    function GetNavigationContent() 
    {
        var filter = "nofilter" + new Date().getTicksUTC();

        $.getJSON('/ajax/NavigationContent.ashx?filter=' + filter, function (data) {
            grid = new Slick.Grid($("#myGrid"), data, columns, options);

            grid.onSort = function (sortCol, sortAsc) 
            {
                sortdir = sortAsc ? 1 : -1;
                sortcol = sortCol.field;

                if (sortAsc == true)
                    data.sort(compare);
                else
                    data.reverse(compare);

                grid.render();
            }; // End Event onSort

        }); // End Function getJSON

    } // End Function GetNavigationContent

</script>









    var columns = [
         { id: "title", name: "Title", field: "title", width: 120, cssClass: "cell-title" }
        , { id: "duration", name: "Duration", field: "duration" }
        , { id: "%", name: "% Complete", field: "percentComplete", width: 80, resizable: false, formatter: Slick.Formatters.PercentCompleteBar }
        , { id: "start", name: "Start", field: "start", minWidth: 60 }
        , { id: "finish", name: "Finish", field: "finish", minWidth: 60 }
        , { id: "effort-driven", name: "Effort Driven", sortable: false, width: 80, minWidth: 20, maxWidth: 80, cssClass: "cell-effort-driven", field: "effortDriven", formatter: Slick.Formatters.Checkmark }
    ];

And this are my options:

   var options = {
        editable: false,
        enableAddRow: false,
        enableCellNavigation: true
    };

    GetNavigationContent();

This is the ajax-handler that generates the test-data:

Imports System.Web
Imports System.Web.Services


Public Class NavigationContent
    Implements System.Web.IHttpHandler, System.Web.SessionState.IRequiresSessionState


    Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest

        context.Response.ContentType = "application/json"

        Dim lsNavigationContent As New List(Of cNavigationRow)

        Dim seed As Random = New Random
        Dim nrThisNavigationRow As cNavigationRow = Nothing
        For i As Integer = 1 To 100 Step 1
            nrThisNavigationRow = New cNavigationRow

            nrThisNavigationRow.title = "Task " + i.ToString()
            nrThisNavigationRow.percentComplete = seed.Next(0, 100)
            nrThisNavigationRow.effortDriven = DirectCast(IIf(i Mod 3 = 0, True, False), Boolean)

            lsNavigationContent.Add(nrThisNavigationRow)
        Next

        Dim strJson As String = Tools.JSON.JsonHelper.Serialize(lsNavigationContent, False)
        context.Response.Write(strJson)
    End Sub


    Public Class cNavigationRow
        Public title As String = "Task 499"
        Public duration As String = "5 days"
        Public percentComplete As Integer = 9
        Public start As DateTime = System.DateTime.Parse("01.01.2009", New System.Globalization.CultureInfo("de-CH", False))
        Public finish As DateTime = System.DateTime.Parse("01.05.2009", New System.Globalization.CultureInfo("de-CH", False))
        Public effortDriven As Boolean = False
    End Class


    ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property


End Class

The problem is, this renders like this (see the date columns):

How do I best format the date columns ?
Inputting it as already formatted string, or is there a better way, supplying the language and/or formatcode ?
If I put it there as string, how will it be sorted ?

Answer 1

SlickGrid can accept Javascript dates. However, you might want to format them as strings before rendering. SlickGrid handles this with the use of column formatters.

For starters take a look at how to use formatters with this example: http://mleibman.github.com/SlickGrid/examples/example2-formatters.html

Now, for dates, you will need to register a custom formatter. You can add this to the slick.formatters.js. Here, I created a custom formatter for parsing a Date object and returning a formatted String:

(function ($) {
  // register namespace
  $.extend(true, window, {
    "Slick": {
      "Formatters": {
        "PercentComplete": PercentCompleteFormatter,
        "PercentCompleteBar": PercentCompleteBarFormatter,
        "YesNo": YesNoFormatter,
        "Checkmark": CheckmarkFormatter,
        "Date": DateFormatter
      }
    }
  });

  function DateFormatter(row, cell, value, columnDef, dataContext) {
    return (value.getMonth()+1)+"/"+value.getDate()+"/"+value.getFullYear();
  }

Now you can use this formatter to format your date columns properly. In your example, your column definition would look like this:

{ id: "start", name: "Start", field: "start", minWidth: 60, formatter: Slick.Formatters.Date }

The dates in the start column look properly formatted now:

As you can see, the sorting works properly too. Let me know if this helps!

Answer 2

I don't think this is a good case for using a custom formatter in SlickGrid. It seems to me that the underlying data is incorrect in this case. The problem is that the Start and Finish fields in the underlying data array are being handled as strings. I really think these should be date/time types.

A custom formatter will certainly solve the problem rendering, but it doesn't really fix the root cause. For example, suppose you want to do some calculation later based on those date values.

The problem is with the call to Tools.JSON.JsonHelper.Serialize(). The method is not serializing the datetime type in a format that can be easily read by the client. Maybe there are some overrides to this method which let you specify the formatting?

Another possibility is to use the built-in ScriptService functionality when calling Json web services in ASP.Net. This is the method I always prefer. For more info, try here.

If neither of these are possible, I think it would be better to parse the date values when they are returned from the Json web service, before binding the data to SlickGrid. Thus ensuring that your dataarray really does contain date/time values.

Just my 2 cents. The custom formatter solution will work just fine if that's really all you need to do.

EDIT: If it helps, here is the function I use to parse dates returned in ASP format.

function parseASPDate(s) {
    if (s) {
        return new Date(parseInt(s.substr(6), 10));
    } else {
        return null;
    }
}

You can find more info here.