Answers

• rf1234 Posts: 2,809Questions: 85Answers: 406

  June 2021 edited June 2021

  https://datatables.net/blog/2014-12-18

  https://datatables.net/plug-ins/sorting/datetime-moment

  This should help you with ordering your date time data.

  You might as well mean something else by "the order is not correct". In case you mean date formatting you could simply use moment.js which is required for the plugin above as well.

  Here is an example from my own coding: Render date and time depending on user language:

  function renderDateTimeSeconds(timestamp) {
      if (lang == 'de') {
          return moment(timestamp).format('DD.MM.YYYY, HH:mm:ss [Uhr]');
      } else {
          return moment(timestamp).format('DD/MM/YYYY, @ hh:mm:ss A');
      }
  }
  

  The parameter "timestamp" is the unformatted date time value as returned from the database.

  For your target format you would probably need this:

  moment(timestamp).format('DD-MM-YYYY HH:mm:ss');
  
• skolbaek Posts: 5Questions: 1Answers: 0

  June 2021

  Hi @rf1234
  I must be doing something wrong .. I have the following in my page:

  js/addons/datatables.min.js
  //cdnjs.cloudflare.com/ajax/libs/moment.js/2.8.4/moment.min.js
  //cdn.datatables.net/plug-ins/1.10.25/sorting/datetime-moment.js

  and have the following script:

  
          $('#dtBasicExample').dataTable( {
            "language": {
                "url": "//cdn.datatables.net/plug-ins/9dcbecd42ad/i18n/Danish.json"
            },
            "columnDefs": [
              { "type": "date", "targets": 0 }
            ]
          
          } );
  
          function renderDateTimeSeconds(timestamp) {
                  return moment(timestamp).format('DD-MM-YYYY HH:mm:ss');
          }
  
  

  but my data sort like this:

  31-05-2021 15:46:39
  31-05-2021 15:43:22
  31-05-2021 15:42:03
  31-05-2021 15:41:44
  ..
  05-05-2021 21:41:52
  05-05-2021 21:43:46
  05-05-2021 21:45:35
  06-06-2021 21:13:17
  07-04-2021 07:39:03
  07-04-2021 07:39:36
  07-04-2021 07:50:20

  Especially look at where "06-06-2021 21:13:17" is placed .. it does not make any sense to me.
• rf1234 Posts: 2,809Questions: 85Answers: 406

  June 2021 edited June 2021

  I don't see you calling the renderer. And I don't see you using the plugin either.

  To use the plugin you would need to do this:

  $.fn.dataTable.moment('DD-MM-YYYY HH:mm:ss');
  

  See the example in the plugin documenation as well please. (Link in my first post.)

  Especially look at where "06-06-2021 21:13:17" is placed .. it does not make any sense to me.

  Well to me it does make sense ... since you don't seem to be using the plugin that would be the normal alphabetical sort order for a string that contains numbers and other characters.
• skolbaek Posts: 5Questions: 1Answers: 0

  June 2021

  My code was somehow cut off .. this should be the complete:

  $(document).ready(function () { 
         $.fn.dataTable.moment( 'DD-MM-YYYY HH:mm:ss' );
  
  
          $('#dtBasicExample').dataTable( {
            "language": {
                "url": "//cdn.datatables.net/plug-ins/9dcbecd42ad/i18n/Danish.json"
            },
            "columnDefs": [
              { "type": "date", "targets": 0 }
            ]
          
          } );
  
          function renderDateTimeSeconds(timestamp) {
                  return moment(timestamp).format('DD-MM-YYYY HH:mm:ss');
          }
  });
  
• rf1234 Posts: 2,809Questions: 85Answers: 406

  June 2021

  you are still not calling the renderer ... Do you need it at all? Or are you getting the formatted date values from the server?
• skolbaek Posts: 5Questions: 1Answers: 0

  June 2021

  The formatted dates are from the server .. ASP translate them to my local

  In SQL they are like:

  2021-05-27 15:23:55.693

  When written to the webpage they are displayed like:

  27-05-2021 15:23:55

  The server does it automatically not my code
• rf1234 Posts: 2,809Questions: 85Answers: 406

  June 2021

  Looks like you also need to use columns.data in order to use a renderer.

  Here is an example from my coding that also uses the renderer:

  ....
  columns: [
          {   data: "log.changer" },
          {   data: "affected_user" },
          {   data: "log.action",
              render: function ( data, type, row ) {
                  return renderAction(data);
              }
          },
          {   data: "update_time",
              render: function (data, type, row) {
                  return renderDateTimeSeconds(data);
              }
          },
          {   data: "log.user_role",
              render: function ( data, type, row ) {
                  return renderRole(data);
              }
          },
          {   data: "user_dept" },
          {   data: "dept_id",
              render: function ( data, type, row ) {
                  return '#_'+ data;
              }
          }
      ],
  ...
  

  In this example "update_time" is read from the server in SQL format and I use my little renderer to format it accordingly.
• rf1234 Posts: 2,809Questions: 85Answers: 406

  June 2021

  When written to the webpage they are displayed like:
  27-05-2021 15:23:55
  The server does it automatically not my code

  Sounds like you don't need a renderer then. Don't know why the ordering plug in doesn't work for you, sorry. Check the console for errors is the first thing I would do.
• skolbaek Posts: 5Questions: 1Answers: 0

  June 2021

  No, I probably do not :-) .. Thank you for your effort though
• kthorngren Posts: 20,425Questions: 26Answers: 4,794

  June 2021 edited June 2021

  The best way forward is with a test case so we can give more pointed help. I started one for you here:
  http://live.datatables.net/jiguwoje/1/edit

  I have this line commented out:

  $.fn.dataTable.moment( 'DD-MM-YYYY HH:mm:ss' );
  

  If you follow the steps in the blog rf1234 linked to you will see the use of the above statement to define the format of the datetime column. With the line commented out the ordering is incorrect. Uncomment the line, click Run JS and the sorting works as expected.

  If you need further help with this please update the test case to show the issue you are trying to solve.

  BTW, you should remove "type": "date". Datatables will automatically figure out the column type and overrride the columns.type setting. The docs provide more details.

  Kevin