dataTables editor plugin DateTime format issue

dataTables editor plugin DateTime format issue

linkrobotsoft@gmail.comlinkrobotsoft@gmail.com Posts: 4Questions: 1Answers: 0

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?

Answers

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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.comlinkrobotsoft@gmail.com Posts: 4Questions: 1Answers: 0

    any example for the 1st suggestion? thanks.

  • linkrobotsoft@gmail.comlinkrobotsoft@gmail.com Posts: 4Questions: 1Answers: 0

    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.comlinkrobotsoft@gmail.com Posts: 4Questions: 1Answers: 0
    edited January 2018

    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

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • idioteque1025idioteque1025 Posts: 1Questions: 0Answers: 0
    edited June 2019

    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.

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

This discussion has been closed.