Editor - How to Get Dates Working in ASP.NET MVC

andyr

I want to edit SQL Server DateTime columns as dates. Below is the best setup I can come up with for the MVC View. This is the rendered HTML. I need " function convertToDate(data)" or dates display in an integer format (see http://datatables.net/forums/discussion/19326/date-time-format.). The date options under "fields" also help with that problem.
But I still have two problems:
1. Clicking a cell sometimes converts the date to today.
2. Saving the data in the Controller sometimes updates the other date field to today.
Can someone help me with this, or with an ASP.NET MVC example using dates and inline editing.

<link href="/Content/site.css" rel="stylesheet"/>   
<script src="/Scripts/modernizr-2.8.3.js"></script>   
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.6/css/jquery.dataTables.css">
<link rel="stylesheet" href="https://cdn.datatables.net/tabletools/2.2.4/css/dataTables.tableTools.css">
 <link rel="stylesheet" href="/Content/dataTables.editor.css">
 <link rel="stylesheet" href="/Content/demo.css">
 ...
<div class="container">
    <!--DataTable javscript code calculates an "auto width"-->
    <table class="display" id="TSSRAvail" cellpadding=" 0" cellspacing="0" border="0" >
        <thead>   
            <tr>
                <th>UIC</th>
                <th>TSSRAvailLocationID</th>
                <th>AvailName</th>
                <th>AvailCode</th>
                <th>AvailStart</th>
                <th>AvailEnd</th>
                <th>FY</th>
            </tr>
        </thead>
    </table>
</div>
...
<script src="/Scripts/jquery-2.1.3.js"></script>
<script src="/Scripts/jquery-ui-1.11.4.js"></script>
<script src="/Scripts/jquery.unobtrusive-ajax.js"></script>
<script src="/Scripts/bootstrap.js"></script>
<script src="/Scripts/respond.js"></script>
<script src="/Scripts/moment.js"></script>
<script src="/Scripts/jquery.dataTables.js"></script>   
<script src="https://cdn.datatables.net/tabletools/2.2.4/js/dataTables.tableTools.js"></script>
<script src="/Scripts/dataTables.editor.js"></script>

<script>
         //--------------------------------------------------------------------------------------------------
        // Call From:  columns..."render" for dateTime columns below.
        // Purpose  :  The Controller returned System.Web.Mvcreturn.Json(). That contained dates in format "/Date(1367218800000)/".
        //             Convert that to a "MM/DD/YYYY" Date(?)
        //-----------------------------------------------------------------------------------------------------
        function convertToDate(data)
        {
            // The 6th+ positions contain the number of milliseconds in Universal Coordinated Time between the specified date and midnight January 1, 1970.
            var dtStart = new Date(parseInt(data.substr(6)));
            // Format using moment.js.
            var dtStartWrapper = moment(dtStart);
            return dtStartWrapper.format("MM/DD/YYYY");
        }
  
     $(document).ready(function () {
            
            var controllerUrl = "/Home/EditTSSRAvailData";
        
            var editor = new $.fn.dataTable.Editor({

                    "ajax": controllerUrl,
                    "table": "#TSSRAvail",

                    // Needed both "dateFormat": "mm-dd-yyyy" and "type": "date for
                    // inline editing to puts dates in <input>s with format "MM/DD/YYYY".
                    "fields": [

                        {
                            "label": "Avail Code",
                            "name": "AvailCode"
                        },
                        {
                            "label": "Avail Start",
                            "name": "AvailStart", 
                            "type": "date",
                            "dateFormat": "mm/dd/yyyy"  
                        },
                        {
                            "label": "Avail End",
                            "name": "AvailEnd",
                            "type": "date",
                            "dateFormat": "mm/dd/yyyy"  
                        }
                    ]
                });  //  end of .Editor()

                $("#TSSRAvail").on("click", "tbody td",
                    function (e) {
                       // debugger;
                        editor.inline(this); 
                    }
                );


                $('#TSSRAvail').DataTable({
                    "dom": "Tfrtip",
                    "ajax": controllerUrl,

                    "columns": [
                        //  Omit the TSSRAvailID field to not display.
                        {
                            "data": "AvailCode"
                        },
                        {
                            "data": "AvailStart",
                            "render": function (data) { return convertToDate(data); }
                        },
                        {
                            "data": "AvailEnd",
                            "render": function (data) { return convertToDate(data); }
                        }
                    ] ,

                    "tableTools": {
                        "sRowSelect": "os",
                        "aButtons": [
                            { "sExtends": "editor_create", "editor": editor },
                            { "sExtends": "editor_edit", "editor": editor },
                            { "sExtends": "editor_remove", "editor": editor }
                        ]
                    }

                }); // end of .DataTable()
            });   // end of $(document).ready.function()

    </script>

allan

Hi,

Are you using the provided .NET libraries for Editor? They have formatting methods which can be used for this sort of thing on the server-side.

Allan

andyr

I am using the .NET libraries. I tried GetFormatter() in the Field() for "AvailStart". Part of the Controller method:

using (var db = new Database(dbType: "sqlserver", str: connString))
      {
          var response = new Editor(db: db,
              table: "TSSRAvail",
              pkey: "TSSRAvailID")
              .Model<EditTSSRAvailModel>()
              ...         
              // You used the Date type for this field in SQL Server.     
              .Field(new Field("TSSRAvail.AvailStart")
              .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_8601))
              ...
              .Process(Request.Form)
              .Data();
           return Json(data: response,
              behavior: JsonRequestBehavior.AllowGet);

1) I see that response.data still contains the AvailStart as "mm/dd/yyyy 12:00:00 AM".
2) I still need my JavaScript convertToDate function or the fields display and edit as "/Date(seconds since 1st Jan 1970?)".
I appreciate any help on using dates for ASP.NET.

allan

Could you show me your EditTSSRAvailModel code please? I'm wondering what date type the TSSRAvail.AvailStart field is. If it is currently a DateTime, could you try it as a string (DateTime should be supported, but its the only thing I can immediately thing of!).

The above code looks okay as best I can tell.

Thanks,
Allan

andyr

1) The "return Json(data: response, behavior: JsonRequestBehavior.AllowGet)" command at the end of the controller method started working better. It no longer converts datetimes to "/Date (seconds since 1st Jan 1970?" in the DataTables/Editor part of the HTML View. I couldn't find what changed on my PC to cause this!

2) In the Controller Method I now have:

...
.Field(new Field("TSSRAvail.AvailStart")
        .GetFormatter(Format.DateSqlToFormat("MM-dd-yyyy"))

3) In the HTML View I commented all special formatting for the datetime fields. I also no longer invoke the jQuery Datepicker; I will deal with that later:

var editor = new $.fn.dataTable.Editor({
...
 {
         "label": "Avail Start",
          "name": "TSSRAvail.AvailStart" 
           //   "type": "date" 
           // "dateFormat": "MM/dd/yyyy"  
  },

$('#TSSRAvail').DataTable({
  ...
 {
     "data": "TSSRAvail.AvailStart"
      // No longer need: "render": function (data) { return convertToDate(data); }
  },

4) The AvailStart Property in the EditTSSRAvailModel is:
public System.DateTime AvailStart { get; set; }
5) I have one question on this setup. What does the .NET GetFormatter function do behind the scenes?

allan

There are two things going on there:

1) The Format.DateSqlToFormat() function returns a delegate (from our other discussion!) which is basically a function that will be run whenever Editor needs to do get formatter on the server (Editor gives it the value to format, the function returns the formatted value).

That function is set up to use the DateTime formatting options that are built into .NET to convert from one format to another. The source is available in the Editor download in the Format.cs file if you are interested in the inner workings of it.

2) The GetFormatter() assigns the delegate to the field and will execute it when Editor requests the field value, passing in the value read from the database (i.e. the one to format).

You could use any delegate function there to perform the require conversion, but the Editor libraries have a few built in to try and make life a little easier!

The Formatters documentation has some further details about what the delegate expects (input parameters) and the built in formatters.

Regards,
Allan

andyr

Thanks for the information. I will use this with dates, and with the Forum Discussion about the Field.Options Method.

andyr

After I used a LeftJoin in the Controller, date columns in inline and form editing formatted again as "/Date (seconds since 1st Jan 1970" :

 // This caused dates to format incorrectly!!!
.LeftJoin(table: "Ship", field1: "Ship.UIC", op:"=", field2:"TSSRAvail.UIC" )
...
   .Field(new Field("TSSRAvail.UIC")
            .Validator(Validation.NotEmpty())

              .Options(
                   () => db
                          .Sql(@"SELECT 
                                      UIC AS value, 
                                      ShipHull AS label
                                      FROM Ship 
                                     WHERE CEMATActive = 1 
                                     AND Class = 'CVN'    
                                    AND Fleet = 'West'  
                                   ORDER BY HullNo")
                  .FetchAll() 

The View (.cshtml):

var editor = new $.fn.dataTable.Editor({
   ...  other Fields       
{
             "label": "Avail Start",
              "name": "TSSRAvail.AvailStart" 
               // Did not fix problem: Edit dates were in form "/Date(1367218800000)/
               // "dateFormat": "MM-dd-yyyy"
          }
...
  $('#TSSRAvail').DataTable( {
       ...  other Columns.
      {
          "data": "TSSRAvail.AvailStart", 
           // Formatted only for display, not for editing
            "render": function (data) { return convertToDate(data); }
        },

andyr

Is there a place to covert the "/Date (seconds since 1st Jan 1970" into a 'MM-dd-yyyy" format before it displays? The "render" seems to only affect the display, the underlying data is still in that funky format.

allan

Ideally you don't really want /Date in the JSON data loaded from the server I would suggest - use a get formatter to transform it into the format that you want at the server.

It is possible to use ajax.dataSrc as a function to transform the source data, but that is really a reserve option and I would recommend that using a get formatter would be a much better way of doing it.

Allan

andyr

The data does not contain /Date. But something in the .NET code adds this. This blog http://www.hanselman.com indicates this happens when the MVC Controller function returns a System.Web.MVC.JsonResult. And Microsoft fixed that in Web API Controllers, but not MVC Controllers.
So the GetFormatter() is not needed.
I added this to the JavaScript on the View. It works for the New/Edit form. And it works for inline-editing, except where the first cell you click is a Date cell. In that case you still see the "/Date(...)." I don't know what to add below to handle that first click.

       editor.on("initEdit", function (e, node, data) {
                // Convert the "data" Date columns from the "/Date(nnnnnnnnnnnnn)/" format.
                if (data.TSSRAvail.AvailStart.slice(0, 5) == "/Date")
                       data.TSSRAvail.AvailStart = convertToDate(data.TSSRAvail.AvailStart);
                if (data.TSSRAvail.AvailEnd.slice(0, 5) == "/Date")
                        data.TSSRAvail.AvailEnd = convertToDate(data.TSSRAvail.AvailEnd);
         });

andyr

I deleted that editor.on(...). I added the below at the end of the statement that creates the DataTable. Seems to work after some quick testing:

            // HomeController.EditTSSRAvailData() returns a JsonResult.  Some "bug" in MVC converts DateTime strings to a "/Date(nnnnnnnnnnnnn)/" format 
            // (number of milisecs. since 1-1-1970). So to work around this, convert the "data" Date columns. Use if statements incase Microsoft fixes this.
            // See "createdRow" example at https://datatables.net/examples/advanced_init/row_callback.html
            "createdRow": function (row, data, index) {
                if (data.TSSRAvail.AvailStart.slice(0, 5) == "/Date")
                    data.TSSRAvail.AvailStart = convertToDate(data.TSSRAvail.AvailStart);
                if (data.TSSRAvail.AvailEnd.slice(0, 5) == "/Date")
                     data.TSSRAvail.AvailEnd = convertToDate(data.TSSRAvail.AvailEnd);
            }

allan

Hi,

I'm slightly confused as you why you suggest that a get formatter is not required? The point of the get formatter is to change the DateTime into a string representation so that the JSON data returned to the client-side does not contain /Date... (which is what I was referring to before).

Thanks,
Allan

andyr

I'd like to use GetFormatter() rather some JavaScript. But the situation is I have two date fields in the "var response = new Editor(db...) command:

.Field(new Field("TSSRAvail.AvailStart")
      .GetFormatter(Format.DateSqlToFormat("MM-dd-yyyy"))
 )
.Field(new Field("TSSRAvail.AvailEnd")
)

I see that "response.data[n]" right after the Editor.Data() contains {[AvailStart, 5/14/2015 12:00:00 AM]} and {[AvailEnd, 5/13/2015 12:00:00 AM]}. Then something in .NET converts those to "/Date (seconds since 1st Jan 1970". So I need that JavaScript
"convertToDate" function in the View/cshtml.
Is "MM-dd-yyyy" valid to pass to DateSqlToFormat?
Can you try a database date column in ASP.NET MVC to see what happens?

allan

The demo .NET package contains a database table called users which contains datetime columns. The StaffController.cs and StaffModel.cs files use those datetime columns and get / set formatters to format the data for the client-side, and that appears to operate successfully.

The StaffModel does have the data type for those columns set to be string as we've discussed before. Did you try setting your model data type to string? It shouldn't make any difference, but it is possible that it will. I'll try it out when I get to my PC later on.

Allan

andyr

Thanks, Allan. I changed the System.DateTime types in the model string types. The only formatting I have left in the .NET Controller or JavaScript is:

.Field(new Field("TSSRAvail.AvailStart")
      .GetFormatter(Format.DateSqlToFormat("MM-dd-yyyy"))
 )
.Field(new Field("TSSRAvail.AvailEnd")
     .GetFormatter(Format.DateSqlToFormat("MM-dd-yyyy"))
)

This works so far. I will post if I have any problems.

andyr

I now tried to get the jQuery Datepicker to work with this. I needed to add to the JavaScript Editor Fields Options:

"dateFormat" : "mm/dd/yy"

If not, the dataTables.editor.js defaults the Datepicker to "D, d M yy", which popups up dates in the year to 2021!
Can you note in the examples and documentation that you need to set that "dateFormat"?

allan

I will do - thanks for the feedback.

Allan