dataTables editor plugin DateTime format issue

linkrobotsoft@gmail.com

I'm using EF6 + MVC for a site. The dataTables editor is used for an UI. One table has a field 'StartDate'. It is a datetime type in the SQL Server.

It works fine until when I try to edit the 'StartDate' value. From the browser debug, I can see that the JSON send from backend to UI is in the timestamp format, i.e. /Date(1541923200000)/ .

In the dataTables, I convert this to the correct local datetime format, so it shows correctly.

However, I could not figure out how to do this in Editor. It always shows the /Date(1541923200000)/ .

The code I use is:

editorAdvertisement = new $.fn.dataTable.Editor({
    ajax: '/APN/GetAdvertisementData',

    table: "#tblAdvertisements",
    fields: [{
        label: "StartDate",
        name: "AdvStartDate"
        , type: "datetime"
        , format: 'MM\/DD\/YYYY h:mm a'
    }, {
        label: "Deadline",
        name: "AdvDeadline"
        , type: "datetime"
    }, {
        label: "TitleOfAdv",
        name: "TitleOfAdv"
    }, {
        label: "ListPrice",
        name: "ListPrice"
    }
    ]
});

var tbl = $('#tblAdvertisements').DataTable({
    pageLength: 10,
    dom: '<"html5buttons"B>lTfgitp',
    ajax: '/APN/GetAdvertisementData'
    ,
    columns: [
        {
            data: "AdvStartDate", name: "AdvStartDate"
            , type: "datetime"
                , render: function (value) {
                    var r = convertDate(value);
                    return r;
                }
            , "autoWidth": true
        },
        {
            data: "AdvDeadline", name: "AdvDeadline"
            , type: "datetime"
            , render: function (value) {
                var r = convertDate(value);
                return r;
            }
            , "autoWidth": true
        },
        { data: "TitleOfAdv", name: "TitleOfAdv", "autoWidth": true },
        {
            data: "ListPrice", name: "ListPrice", "autoWidth": true
            , render: $.fn.dataTable.render.number(',', '.', 0, '$')
        }
    ],
    order: [1, 'asc'],
    select: {
        style: 'os',
        selector: 'td:first-child'
    },
    buttons: [
        { extend: "create", editor: editorAdvertisement }
        , { extend: "edit", editor: editorAdvertisement }
        , { extend: "remove", editor: editorAdvertisement }

    ]
    , select: true
    , searching: false
    , paging: false

});

In the controller

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

    using (var db = new Database(settings.DbType, settings.DbConnection))
    {
        var response = new Editor(db, "Advertising", new[] { "AdvertisingID" })
            .TryCatch(false)
            .Model<Advertising2>()
            .Field(new Field("AdvStartDate")
                .Validator(Validation.DateFormat(
                    "MM/dd/yyyy",
                    new ValidationOpts { Message = "Please enter a date in the format MM/dd/yyyy" }
                ))
                .GetFormatter(Format.DateTime("yyyy-MM-dd HH:mm:ss", "MM/dd/yyyy"))
                .SetFormatter(Format.DateTime("MM/dd/yyyy", "yyyy-MM-dd HH:mm:ss"))
            )
            .Field(new Field("AdvDeadline")
                .Validator(Validation.DateFormat(
                    "MM/dd/yyyy",
                    new ValidationOpts { Message = "Please enter a date in the format MM/dd/yyyy" }
                ))
                .GetFormatter(Format.DateSqlToFormat("MM/dd/yyyy"))
                .SetFormatter(Format.DateFormatToSql("MM/dd/yyyy"))
            )

            .Field(new Field("TitleOfAdv"))
            .Field(new Field("ListPrice"))

            .Process(request)
            .Data();

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

How to fix this?

allan

Editor will edit the "raw" value, so the best option is actually to use a formatter at the server-side to convert it into the format that you want - then DataTables and Editor will only see and use the formatted value.

The other option would be to use initEdit - you could get the value there, transform it and set it back in the now formatted value.

Allan

linkrobotsoft@gmail.com

any example for the 1st suggestion? thanks.

linkrobotsoft@gmail.com

also, I'm just curious, why in the demo (generated by generator), the datetime column, when use the C# MVC, the

new Edito(db, .....

part is the same as what I'm using, why at the end

return Json(response, JsonRequestBehavior.AllowGet);

returns the datetime as yyyy-MM-dd format instead of the /Date(....)/ format I got. Is it because the generator uses a different dll for the JSONResult?

linkrobotsoft@gmail.com

if use the 2nd method, how to change the data value? below, d is the correct format, but the data seems not changed.

editorAdvertisement.on('initEdit', function (e, node, data) {
var d = convertDate(data.AdvStartDate);
data.AdvStartDate = d;
return data;
});

Thanks

allan

Hi,

Yes, an example of the first one is included in the .NET download (this is it in PHP).

Have you used Date as the data type for that field in the modal? If so, if you change it to be string does it then work? Can you show me the Editor code you are using on the server-side please?

Thanks,
Allan

idioteque1025

I know this is an old ticket but I still think it would be nice if the date/datetime picker supported a "rawformat" and and "displayFormat". Doing the validation on the server side is not always ideal. Especially when all datetimes in the DB are the same and all display values in the UI could be different.

allan

Colin agrees, and as this crops up a few times I'm coming around to it as well. I've filed an enhancement bug for this. Likely I'll call the option for it wireFormat (input and output to be sent over the "wire").

That said:

Doing the validation on the server side is not always ideal

Its not always ideal, but it is fundamentally required for security. Client-side validation is trivial to bypass.

Thanks,
Allan