Using GetFormatter to Convert JsonDate to date string

rdm

In my situation, I am using Editor in an ASP.NET 5 application (.NET Framework) with a SQL Server 2014 database.

I have everything defined just as I need it... except for that pesky Json datetime format issue. While there are many posts here and in Stack Overflow talking about converting the .NET Json datetime format (e.g., /Date(1538377200000)/) to a date format (e.g, 10/1/2018), those questions focus on rendering DataTables cell data in the desired format.

Using the DataTables configuration, I can render the dates so that they appear as desired in the table (see screenshot).

My problem is when I add or edit a record. The json format date is shown (see screenshot).

How can I change the formatting in the New/Edit dialog so that the dates appear in date format rather than JSON datetime format? I figure I need a SetFormatter, but I haven't yet found any examples for this specific operation. Maybe that's the missing item?

The code style you see comes from existing Editor documentation and a few helpful Stack Overflow answers.

Editor code in Controller

[AcceptVerbs(HttpVerbs.Get | HttpVerbs.Post)]
public ActionResult EditorTable()
{
    var settings = Properties.Settings.Default;
    var formData = HttpContext.Request.Form;

    using (var db = new Database(settings.DbType, settings.DbTaskManagementConnection))
    {
        var response = new Editor(db, "Tasks", "Id")
            .Model<Tasks>()
            .Field(new Field("Project"))
            .Field(new Field("WindowDateStart") 
                .SetFormatter((val, data) => 
                    DeserializeObject<DateTime>(WrapStringInQuotes(val.ToString())))                
            )
            .Field(new Field("WindowDateEnd")
                .SetFormatter((val, data) => 
                    DeserializeObject<DateTime>(WrapStringInQuotes(val.ToString()))))
            .Field(new Field("Status"))
            .Field(new Field("Task"))
            .Process(formData)
            .Data();

        return Json(response, JsonRequestBehavior.AllowGet);
    }
}

**jQuery code in View **

var editor;
$(() => {
    $.fn.dataTable.moment('M/D/YYYY');

    function convertJsonDateToShortDate(data) {
        // This function converts a json date to a short date
        // e.g. /Date(1538377200000)/ to 10/1/2018
        
        const dateValue = new Date(parseInt(data.substr(6)));
        return dateValue.toLocaleDateString();
    }

    editor = new $.fn.dataTable.Editor({
        ajax: "@Url.Action("EditorTable")",
        table: "#Tasks",
        order: [[1, "asc"]],
        fields: [
            { 
                label: "Project", 
                name: "Project"
            },
            { 
                label: "Window Date Start", 
                name: "WindowDateStart", 
                type:"datetime", 
                format: "MM/DD/YYYY"
            },
            {
                 label: "Window Date End", 
                 name: "WindowDateEnd", 
                 type: "datetime", 
                 format: "MM/DD/YYYY"
            },
            {
                 label: "Status", 
                 name: "Status"
            },
            {
                 label: "Task", 
                 name: "Task"
            }
        ],
        columns: [
            { data: "Project" },
            { data: "WindowDateStart" },
            { data: "WindowDateEnd" },
            { data: "Status" },
            { data: "Task" }
        ]
    });

    var dataTableConfig = {
        dom: "Blftipr",
        buttons: [
            "excelHtml5",
            "print",
            { extend: "create", editor: editor },
            { extend: "edit", editor: editor },
            { extend: "remove", editor: editor }
        ],
        ajax: {
            url: "@Url.Action("EditorTable")",
            type: "POST"
        },
        columns: [
            { data: "Project" },
            { 
                data: "WindowDateStart", 
                render: function(data) {
                    //This formats json dates as shortdates in the table
                    return convertJsonDateToShortDate(data);
                }
            },
            {
                 data: "WindowDateEnd", 
                 render: function(data) {
                     //This formats json dates as shortdates in the table
                     return convertJsonDateToShortDate(data);
                 }
            },
            { data: "Status" },
            { data: "Task" }
        ],
        select: {
            style: "os",
            selector: "td"
        }
    };

    $('#Tasks').DataTable(dataTableConfig);

});

In case you're interested what the SQL Server side looks like, it's in a very simple state.

use TaskManagement;

IF OBJECT_ID('dbo.Tasks', 'U') IS NOT NULL
    DROP TABLE dbo.Tasks;
GO

CREATE TABLE dbo.Tasks (
    Id INT NOT NULL identity(1, 1),
    Project NVARCHAR(50) NOT NULL,  
    WindowDateStart date NOT NULL,
    WindowDateEnd date NOT NULL,
    [Status] NVARCHAR(50) NOT NULL,
    Task NVARCHAR(max) NOT NULL,
    CONSTRAINT PK__Tasks PRIMARY KEY CLUSTERED ([Id] ASC) WITH (
        PAD_INDEX = OFF,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON
        )
    );

INSERT INTO dbo.Tasks (
    Project,
    WindowDateStart,
    WindowDateEnd,
    [Status],
    Task
    )
VALUES (
    'Alt Ed Application',
    '10/1/2018',
    '12/15/2018',
    'On track',
    'Copy summary values to static spreadsheets'
    ),
    (
    'CRDC',
    '10/1/2018',
    '12/15/2018',
    'On track',
    'Get course enrollment records from last year''s archived Schoolmaster.'
    );

SELECT *
FROM dbo.Tasks

Thank you

rdm

So I tried this in the Editor code.

.Field(new Field("WindowDateStart")
    .SetFormatter((val, data) => 
        DeserializeObject<DateTime>(WrapStringInQuotes(val.ToString())))
    .GetFormatter((val, data) => ((DateTime)val).ToShortDateString())
    )

By doing a debug session, I know that the GetFormatter is returning a Short Date String (e.g., 10/11/2018), and yet I'm still getting JSON date format when editing date records.

rdm

I tried deconstructing GetFormatter a little more. On the C# side, it looks to me as if I am seeing the correct data returned.

.GetFormatter((val, data) =>
{
    var date = Convert.ToString(val);
    var tDate = date.Remove(date.Length - 9);
    Debug.WriteLine($"date: {date}, tDate: {tDate}");  
    // val appears as datetime 10/1/2018 00:00:00
    // tDate appears as short date string 10/1/2018
    return tDate;
})

In the Visual Studio output (when I run the app in debug mode) I see:

date: 10/2/2018 00:00:00, tDate: 10/2/2018
date: 12/15/2018 00:00:00, tDate: 12/15/2018

However, when I look in Chrome developer tools, I see this in the Network > XHR screen:

When loading the page

data: [{DT_RowId: "row_1", Project: "Alt Ed Application", WindowDateStart: "/Date(1538463600000)/",…},…]
    0: {DT_RowId: "row_1", Project: "Alt Ed Application", WindowDateStart: "/Date(1538463600000)/",…}
        DT_RowId: "row_1"
        Id: 1
        Project: "Alt Ed Application"
        Status: "On track"
        Task: "Copy summary values to static spreadsheets"
        WindowDateEnd: "/Date(1544857200000)/"
        WindowDateStart: "/Date(1538463600000)/"

And when I'm editing a page

data: [{DT_RowId: "row_1", Project: "Alt Ed Application", WindowDateStart: "/Date(1538463600000)/",…}]
    0: {DT_RowId: "row_1", Project: "Alt Ed Application", WindowDateStart: "/Date(1538463600000)/",…}
        DT_RowId: "row_1"
        Id: 1
        Project: "Alt Ed Application"
        Status: "On track"
        Task: "Copy summary values to static spreadsheets"
        WindowDateEnd: "/Date(1540623600000)/"
        WindowDateStart: "/Date(1538463600000)/"

So it appears that something else is transforming the date string to Json dates outside of the Editor ActionResult.

rdm

I finally got this to work. The key was in the fields section of the $.fn.dataTable.Editor() function.

I only have SetFormatter code in the MVC controller.

function convertJsonDateToShortDate(data) {
    // This function converts a json date to a short date
    // e.g. /Date(1538377200000)/ to 10/1/2018
    const dateValue = new Date(parseInt(data.substr(6)));
    return dateValue.toLocaleDateString();
}

var url = "@Url.Action("EditorTable")";

editor = new $.fn.dataTable.Editor({
    ajax: url,
    table: "#Tasks",
    order: [[1, "asc"]],
    fields: [
        {
            label: "Project",
            name: "Project"
        },
        {
            label: "Window Date Start",
            name: "WindowDateStart",
            type: "datetime",
            format: "MM/DD/YYYY",
            data: function(data) {
                const $t = data;
                $.each($t, function (a, b) {
                    if (a === "WindowDateStart") {
                        console.log(data[a]); // value arrives in json format
                        data[a] = convertJsonDateToShortDate(b);
                        console.log(data[a]); // proof that value converted to short date
                    }
                });
                return data.WindowDateStart; // THIS IS THE KEY! Return only the specific parameter.
            }
        },
        {
            label: "Window Date End",
            name: "WindowDateEnd",
            type: "datetime",
            format: "MM/DD/YYYY",
            data: function (data) {
                const $t = data;
                $.each($t, function (a, b) {
                    if (a === "WindowDateEnd") {
                        console.log(data[a]); // value arrives in json format
                        data[a] = convertJsonDateToShortDate(b);
                        console.log(data[a]); // proof that value converted to short date
                    }
                });
                return data.WindowDateEnd; // THIS IS THE KEY! Return only the specific parameter.
            }
        },

ryetgr

You are an amazing human. Thank you very much for saving me so much time with this