jQuery dataTables编辑器插件DateTime格式问题

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.

我在网站上使用EF6 + MVC。 dataTables编辑器用于UI。一个表有一个字段'StartDate'。它是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)/ .

它工作正常,直到我尝试编辑'StartDate'值。从浏览器调试中,我可以看到从后端发送到UI的JSON采用时间戳格式,即/ Date(1541923200000)/。

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

在dataTables中,我将其转换为正确的本地日期时间格式,因此它显示正确。

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

但是,我无法弄清楚如何在Editor插件中执行此操作。它始终显示/日期(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);
    }
}

I could not figure it out after a long search. Anyone had the same issue? Any solution?

经过长时间的搜索,我无法弄明白。有人有同样的问题吗?有解决方案吗

2 个解决方案

#1

I had the same problem and eventually found the answer on the Editor forum.

我遇到了同样的问题,最终在编辑论坛上找到了答案。

If you look with fiddler (a great tool for seeing what happens) you see the JSON has this format you get. If you look in the code in visual studio express and capture what you got before passing it to JSON you see something like "{12/12/2012 12:12}". So actually the cause is the JSON.

如果你看一下fiddler(一个看看会发生什么的好工具),你会发现JSON有这种格式。如果您查看visual studio express中的代码并在将其传递给JSON之前捕获您所获得的内容,您会看到类似“{12/12/2012 12:12}”的内容。所以实际上原因是JSON。

Refactoring to just the essentials in your first column it should read like this.

重构到第一列中的要点,它应该是这样的。

This approach solved my problem without any problem. This version is designed to render null dates as blanks, otherwise format as directed. (Update: refactored the answer again, seeing that the next morning the formatted codes were not behaving as expected.)

这种方法没有任何问题解决了我的问题。此版本旨在将空日期呈现为空白,否则按指示格式化。 (更新:再次重构答案,看到第二天早上格式化的代码没有按预期运行。)

{ 
   data: "AdvStartDate", 
   render: function (data, type, full) {
          if (data != null) {
                 var dtStart = new Date(parseInt(data.substr(6)));
                 return dtStart.toLocaleDateString();
          } else {
                 return "";
          }
   }

#2

Check Out moment.js (https://momentjs.com), download moment-with-locales.js and add it to your ScriptBundle in BundleConfig.cs.

查看moment.js(https://momentjs.com),下载moment-with-locales.js并将其添加到BundleConfig.cs中的ScriptBundle。

Then in your datatable's javascript code you can render the column in proper date/time format as follows. Note: my locale is 'pt' (Portugal). The date column is rendered as dd/mm/yyyy hh:mm (once again, see the formatting options in https://momentjs.com).

然后在您的数据表的javascript代码中,您可以按正确的日期/时间格式呈现列,如下所示。注意:我的语言环境是'pt'(葡萄牙)。日期列呈现为dd / mm / yyyy hh:mm(再次,请参阅https://momentjs.com中的格式选项)。

"columns": [
        { "data": "OriginName" },
        { "data": "SmsID" },
        { "data": "DestinationNumber" },
        {
            "data": "SMSDeliveryDate",
            "render": function (data) {
                var re = /-?\d+/;
                var m = re.exec(data);
                var d = new Date(parseInt(m[0]));
                moment.locale('pt');
                var m = moment(d).format('L LTS');
                return m;
            }
        },
        { "data": "SmsStateDesc" }
    ],

Hope this info can be useful. I too was stuck with this for a few hours...

希望这些信息有用。我也被困在这几个小时......

José

#1