Answers

• allan Posts: 63,498Questions: 1Answers: 10,471 Site admin

  November 21

  Can you show me an example of the JSON data that is being loaded for the rows? Your GetFormatter looks like it should result in the data being in YYYY-MM-DD format, which is ideal.

  Datatables is string sorting, not date sorting

  Use the datetime renderer - at the moment you are returning a string for all types - hence why it is string sorting. The renderer will handle the formatting for you:

  render: DataTable.render.datetime('MM-DD-YYYY')
  

  Example here.

  displayFormat

  Could you try:

              displayFormat: 'MM-DD-YYYY',
              wireFormat: 'YYYY-MM-DD',
  

  please?

  It will depend on what the server is returning, but hopefully that will help. If not, it would be really useful if you could link to a page showing the issue so I can help to debug it and see the relevant data.

  Allan
• wadeparallon Posts: 64Questions: 9Answers: 0

  November 21

  Server is returning this:

  {"draw":null,"data":[{"DT_RowId":"row_1","Id":1,"StartDate":"2018-06-01T00:00:00","EndDate":"2018-09-01T00:00:00" ....

  Which it shows in the input field.

  I apologize but right now the page only exists in local development. There is nothing special about the date being returned. Its a SQL Server Datetime, to a C# DateTime property.
• wadeparallon Posts: 64Questions: 9Answers: 0

  November 21

  I tried the wireFormat before, but might have not tried it with the current puzzle pieces in place.

  Let me double check on that property.
• wadeparallon Posts: 64Questions: 9Answers: 0

  November 21

  From your comments it looks like the .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_8601 )) isn't returning what its supposed to.

  {
                  data: "StartDate",
                  title: "StartDate",
                  render: function(data, type, row) {
                      return DataTable.render.datetime('YYYY-MM-DD');
                  }
              },
  

  Makes the Datatables js show blank.

  {
                  label: 'EndDate',
                  name: 'EndDate',
                  type: 'datetime',
                  def: () => new Date(),
                  displayFormat: 'MM-DD-yyyy',
                  wireFormat: 'YYYY-MM-DD'
                  
              }
  

  Makes the Editor input field show blank.

  
• wadeparallon Posts: 64Questions: 9Answers: 0

  November 21

  Just like to add that I have been looking at the DatesController and the DateTimeController in the examples in .NET package and I feel like I've done the same thing as those controllers.

  I'll continue to examine.. but as of right now I'm still at a loss.
• wadeparallon Posts: 64Questions: 9Answers: 0

  November 21

  I've replicated the code exactly whats going on in datescontroller but no luck.

  Here are my includes:
  
• allan Posts: 63,498Questions: 1Answers: 10,471 Site admin

  November 22

  As you say, the response from the server-side doesn't look right. The DATE_ISO_8601 token is the correct one to use. I'm not sure why that isn't working!

  What version of the dll are you using? Also, what is the data type of the column in the database?

  Allan
• wadeparallon Posts: 64Questions: 9Answers: 0

  November 22 edited November 22

  @allan thank you for trying to figure this out. Here is where I'm sitting now. I can successfully change the date format from the Datatable JS in the datatable, but I can never seem to touch the format of the date in the input (contrary to the example I'm mimicking /examples/dates/dates.html)

  Editor DLL is 2.3.2.0

  Database (StartDate/EndDate Columns)
  
  

  Code (modeled off the datescontroller example)
  

  Data returned:
  

  Datatable JS (modeled off of dates.html example)

  $(document).ready(function () {
      const tableData = document.querySelector("#PlanningPeriodsDatatable")
      
      const editor = new DataTable.Editor({
          ajax: '/Organizations/'+tableData.dataset.organizationid+'/PlanningPeriods/Editor',
          idSrc:  'Id',
          formOptions: {
              main: {
                  focus: 'none'
              }
          },
          fields: [
              {
                  label: 'StartDate',
                  name: 'StartDate',
                  type: 'datetime',
                  def: function () {
                      return new Date();
                  }
              },
              {
                  label: 'EndDate',
                  name: 'EndDate',
                  type: 'datetime',
                  def: function () {
                      return new Date();
                  }
              }
          ],
          table: '#PlanningPeriodsDatatable'
      });
  
      const datatable = new DataTable("#PlanningPeriodsDatatable", {
          ajax: {
              url: "/Organizations/"+tableData.dataset.organizationid+"/PlanningPeriods/Editor",
              type: "GET",
              dataType: "JSON"
          },
          order: [2, "asc"],
          columns: [
              {
                  data: null,
                  orderable: false,
                  searchable: false,
                  render: DataTable.render.select(),
                  width: '40px'
              },
              {
                  data: "Id",
                  title: "Id",
                  visible: false,
                  searchable: false
              },
              {
                  data: "StartDate",
                  title: "StartDate"
              },
              {
                  data: "EndDate",
                  title: "EndDate"
              }
          ],
          "initComplete": function () {
              $(this).css("opacity", 1);
          },
          layout: {
              topStart: {
                  buttons: [
                      { extend: 'create', editor: editor },
                      { extend: 'edit', editor: editor }
                  ]
              }
          },
          select: {
              style: 'single',
              selector: 'td:first-child'
          }
      });
  });
  

  C# Model:

      public DateTime StartDate { get; set; }
      public DateTime EndDate { get; set; }
  

  Output
  

  Side note: I noticed that the column is LEFT aligned when its reading it as a string, but when its reading it as a datetime, everything is RIGHT aligned. Is this intended?
• wadeparallon Posts: 64Questions: 9Answers: 0

  November 22

  Hmm... just noticed my datetime column... there is a slight difference:

   datetime2(7)  2024-06-01 00:00:00.0000000
   datetime      2011-12-31 00:00:00.000
  
• allan Posts: 63,498Questions: 1Answers: 10,471 Site admin

  November 22

  Hmmm - I'll set up a test database here with a datetime2(7) - I'm wondering if that is the issue. My tests use datetime and I wonder if the formatter isn't quite coping with the different time (although I would have thought it would map to a DateTime and this suggests that it does).

  Sorry I don't have an immediate answer - there will be a solution though! As I say, I'll try a test with datetime2(7) here.

  Allan
• wadeparallon Posts: 64Questions: 9Answers: 0

  November 22 edited November 22

  Hey no problem. I think we've narrowed down the issue.

  Just an FYI, this is the default datatype in sql server being set by Entity Framework in code first these days. At least thats what its looking like.

  So if we can improve going forward or at least figure out how to handle it in that format.. then progress is progress. And I'll be happy to have helped.