how to format date in jQuery DataTables

Question

I am using jQuery DataTables and I have multiple columns with dates, the current data is in this format 2020-06-18 14:32:45.707 and I want to format it and display it as 18/06/2020 14.32.

I applied datetime plugin in DataTables, but still can't make it work.

Currently I am using :

render: function(data) {
  return moment(data).format('DD/MM/YYYY HH:mm');
}

Which is working fine. But I want to use render:

render: $.fn.dataTable.render.moment('DD/MM/YYYY HH:mm')

I have included moment.js and datetime.js as the documentation says and I should apply:

$.fn.dataTable.render.moment(to);

My dates are shown as 'invalid date' in my table when i use this method. below is a demo.

Could you please explain me what am I doing wrong with?:

$.fn.dataTable.render.moment('DD/MM/YYYY HH:mm')

I have the other method working, but I want to learn from my mistakes as I spend much time investigating and couldn't figure out the issue. Thank you very much.

$(document).ready(function() {
  $('#example').DataTable({
    "columnDefs": [{
      //render: $.fn.dataTable.render.moment( 'DD/MM/YYYY HH:mm' )
      "render": function(data) {
        return moment(data).format('DD/MM/YYYY HH:mm');
      },
      "targets": 1
    }]
  });
});

<link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.css" rel="stylesheet" />
<link href="https://cdn.datatables.net/1.10.21/css/dataTables.bootstrap4.min.css" rel="stylesheet" />
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.21/js/dataTables.bootstrap4.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.26.0/moment.min.js"></script>
<script src="https://cdn.datatables.net/plug-ins/1.10.21/dataRender/datetime.js"></script>

<table id="example" class="table table-bordered" style="width:100%">
  <thead>
    <tr>
      <th>date before format</th>
      <th>date after format</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>2020-06-18 14:32:45.707</td>
      <td>2020-06-18 14:32:45.707</td>
    </tr>
  </tbody>
</table>

Solution

Your issue is here:

// Argument shifting
if (arguments.length === 1) {
  locale = 'en';
  to = from;
  from = 'YYYY-MM-DD';
}

The default FROM is 'YYYY-MM-DD', you need to specify YOUR source format.

const FROM_PATTERN = 'YYYY-MM-DD HH:mm:ss.SSS';
const TO_PATTERN   = 'DD/MM/YYYY HH:mm';

$(document).ready(function() {
  $('#example').DataTable({
    columnDefs: [{
      render: $.fn.dataTable.render.moment(FROM_PATTERN, TO_PATTERN),
      targets: 1
    }]
  });
});

<link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.css" rel="stylesheet" />
<link href="https://cdn.datatables.net/1.10.21/css/dataTables.bootstrap4.min.css" rel="stylesheet" />
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.21/js/dataTables.bootstrap4.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.26.0/moment.min.js"></script>
<script src="https://cdn.datatables.net/plug-ins/1.10.21/dataRender/datetime.js"></script>

<table id="example" class="table table-bordered" style="width:100%">
  <thead>
    <tr>
      <th>date before format</th>
      <th>date after format</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>2020-06-18 14:32:45.707</td>
      <td>2020-06-18 14:32:45.707</td>
    </tr>
  </tbody>
</table>

Comments

[Mr. Polywhirl]

I do not think you can in-line the function without providing a callback?

[andrewJames]

If you only use this $.fn.dataTable.render.moment(to);, then it assumes the "from" date is already formatted as an ISO 8601 datetime. But your source data is this: 2020-06-18 14:32:45.707 - which is not an ISO 8601 date time (there is no T between the date and the time). So use the $.fn.dataTable.render.moment( from, to ); form instead.

[DevTN]

thank you very much. I understood the issue now. much appreciated.

[Willy]

If the date from is microsoft json date /Date(1515351600000)/, how do we set FROM_PATTERN?

[Mr. Polywhirl]

@Willy M$ date is archaic You will need to pre-process the date, before formatting. See: stackoverflow.com/a/2316066