format date as dd/mm/yyyy in datatable column in Javascript/Jquery

Question

I have a date in the first column of the datatable, and I need it to be sortable as date. The problem is that when I format it as dd/mm/yyyy then the column sort as a string and dont take into account the month, it sorts just by day that is the first character in the string.

Then the data in the table may appears as:

---

05-11-2019

09-10-2019

11-10-2019

21-09-2019

---

And is not correct.

The code I use to format is as dd/mm/yyyy, is this:

JS

function formatDate(date) {
    var d = new Date(date),
        month = '' + (d.getMonth() + 1),
        day = '' + d.getDate(),
        year = d.getFullYear();

    if (month.length < 2)
        month = '0' + month;
    if (day.length < 2)
        day = '0' + day;

    return [day, month, year].join('-');
}

And the code to append it in the table is this one:

JS

$tblRow.append('<td style="text-align: center;" id="td' + i + '">' + formatDate(ordersList[i][4]) + '</td>');

How can I format it as dd/mm/yyyy and that it can be sortable by day and month.

Answer 1

You can add a custom sort method using Jquery Extend and fn.dataTableExt.oSort as follows:

$(document).ready(function() {
function convertDate(d) {
var p = d.split(/\D/g)
return [p[2],p[1],p[0] ].join("/")
}
let json = [
  {
    "item": "Someitem",
    "Price": 120,
    "Status": "Free",
    "date": "2019-11-01"
},
{
    "item": "SecondItem",
    "Price": 90,
    "Status": "Taken",
    "date": "2019-10-01"
},
{
    "item": "ThirdItem",
    "Price": 90,
    "Status": "Taken",
    "date": "2019-10-15"
}
  ];

  var columnDefs = [
  {
    data: "item"
  }, 
  {
    data: "Price"
  }, 
  {
    data: "Status"
  },
  {
    data: "date"
  }
  ];

  var myTable;

  	 json.forEach((item)=>{
     item.date = convertDate(item.date);
     })
     myTable = $('#example').DataTable({
    "sPaginationType": "full_numbers",
    data: json,
    columns: columnDefs,
    responsive: false,
    columnDefs: [
						{"className": "dt-center", "targets": "_all"},
						{
							"targets": [ 3 ],
							"visible": true,
							"searchable": false,
              "sType": "date-ddmmyyyy"
						}
            ]
  });
jQuery.extend( jQuery.fn.dataTableExt.oSort, {
"date-ddmmyyyy-pre": function ( a ) {
    var dateFormat = a.split('/');
    return (dateFormat[2] + dateFormat[1] + dateFormat[0]) * 1;
},

"date-ddmmyyyy-asc": function ( a, b ) {
    return ((a < b) ? -1 : ((a > b) ? 1 : 0));
},

"date-ddmmyyyy-desc": function ( a, b ) {
    return ((a < b) ? 1 : ((a > b) ? -1 : 0));
}
});
})

table.dataTable tbody>tr.selected,
table.dataTable tbody>tr>.selected {
  background-color: #A2D3F6;
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link href="https://cdn.datatables.net/1.10.11/css/jquery.dataTables.min.css" rel="stylesheet"/>
<script src="https://cdn.datatables.net/1.10.11/js/jquery.dataTables.min.js"></script>

<div class="container">
  <table cellpadding="0" cellspacing="0" border="0" class="dataTable table table-striped" id="example">
  <thead>
    <th>name</th>
    <th>price</th>
    <th>status</th>
    <th>date</th>
  </thead>
  </table>
</div>

To do this you need to create the sort after the table initialization:

jQuery.extend( jQuery.fn.dataTableExt.oSort, {
"date-ddmmyyyy-pre": function ( a ) {
    var dateFormat = a.split('/');
    return (dateFormat[2] + dateFormat[1] + dateFormat[0]) * 1;
},

"date-ddmmyyyy-asc": function ( a, b ) {
    return ((a < b) ? -1 : ((a > b) ? 1 : 0));
},

"date-ddmmyyyy-desc": function ( a, b ) {
    return ((a < b) ? 1 : ((a > b) ? -1 : 0));
}
} );

Also in your columnsDef you need to add the search type of the date column:

{
    "targets": [ 3 ],
    "visible": true,
    "searchable": false,
    "sType": "date-ddmmyyyy"//<--- this one
}

Hope it helps

Answer 2

You can use mRender function to display date in dd/mm/yyyy format and keep type of column as date only so that by default it will sort by using dates.

$("#example").dataTable({
  "aaData":[
  ["2013-10-15 10:30:00"],
  ["2014-01-10 12:00:00"],
  ["2013-09-10 12:00:00"],
  ["2018-01-10 12:00:00"],
  ["2013-01-10 12:00:00"]
  ],
  // property decides how each column will be rendered in table 
  "aoColumnDefs":[
  
  {
    "aTargets":[0]
    , "sType": "date" // type of the column
    , "mRender": function(d, type, full) {
      d = new Date(d);      
      month = '' + (d.getMonth() + 1),
      day = '' + d.getDate(),
      year = d.getFullYear();

      if (month.length < 2)
        month = '0' + month;
      if (day.length < 2)
        day = '0' + day;

      return [day, month, year].join('-');
    }  
  }
  ]
});

<script type="text/javascript" charset="utf8" src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.2.min.js"></script>
<script type="text/javascript" charset="utf8" src="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"></script>
<link rel="stylesheet" type="text/css" href="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css">
<table id="example">
  <thead>
    <tr><th>Dates</th></tr>
  </thead>
  <tbody>
  </tbody>
</table>