Date column sorting not working for H24 format

Date column sorting not working for H24 format

MajesticMajestic Posts: 14Questions: 7Answers: 0

Hello,
I use a simpleDateFormat "yyyy-MM-dd hh:mm:ss.SSS" to display a date in my datatable. I've to change this format to "yyyy-MM-dd HH:mm:ss.SSS" to have the 24H format.
But with this new format, the sort on date column is not working anymore, instead of a sort by date, it is doing an alphabetical sort. Although it was working with the previous format.
It is a matter of plugin I have to use or about code ?

This is my datatable :

$('#dataTables').DataTable({
    responsive: false,
    ajax: {
        "url": restURI + 'orch/search/events/',
        "contentType": "application/json",
        "type": "POST",
        "data": function ( d ) {
            return JSON.stringify(getDatasToSearch());
        },
        error: function(jqXHR, textStatus, errorThrown){
            manageAjaxError(jqXHR, textStatus, errorThrown)
        }
    },
    buttons: [
     'selectAll',
     'selectNone'
     ],
     language: {
         buttons: {
             selectAll: "Select all items",
             selectNone: "Select none"
         }
     },
     select: {
        selector: 'td:first-child',
        style: 'multi'
    },
    order: [0, 'desc'],
    scrollCollapse: true,
    scrollX: false,
    aLengthMenu: [
        [10, 25, 50, 100, -1],
        [10, 25, 50, 100, "All"]
    ],
    iDisplayLength: 25,
    "columns": [
        { "data": "EVENT_ID", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "FLOW_ID", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "FLOW_NAME", "defaultContent": "", "sClass": "text-center limitedWidthFlNm" },
        { "data": "OBJECT_TYPE", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "OBJECT_NAME", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "OBJECT_VERSION", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "OBJECT_ITERATION", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "OPERATION_NAME", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "OPERATION_STATUS", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "OPERATION_MESSAGE", "defaultContent": "", "sClass": "text-center limitedWidth" },
        { "data": "CREATE_DATE", "defaultContent": "", "sClass": "text-center limitedWidth","sType":"date" },
        { "data": "UPDATE_DATE", "defaultContent": "", "sClass": "text-center limitedWidth " ,"sType":"date" }
    ]
})

Thank you for your help !

This question has accepted answers - jump to:

Answers

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    Answer ✓

    This Date Time Sorting Blog should help.

    Kevin

  • MajesticMajestic Posts: 14Questions: 7Answers: 0

    Thank you for your quick replay. I saw this pluging, but when I use it I have an error :

    Uncaught TypeError: Cannot read property 'length' of null
    at jquery.dataTables.min.js:48
    at i (jquery.dataTables.min.js:35)
    at Object.success (jquery.dataTables.min.js:35)
    at i (jquery.min.js:2)
    at Object.fireWith [as resolveWith] (jquery.min.js:2)
    at A (jquery.min.js:4)
    at XMLHttpRequest.<anonymous> (jquery.min.js:4)

    This is my datatable definition :

    $.fn.dataTable.moment( 'yyyy-MM-dd HH:mm:ss:SSS' );
    $('#dataTables').DataTable({
        responsive: false,
        ajax: {
            "url": restURI + 'orch/search/events/',
            "contentType": "application/json",
            "type": "POST",
            "data": function ( d ) {
                return JSON.stringify(getDatasToSearch());
            },
            error: function(jqXHR, textStatus, errorThrown){
                manageAjaxError(jqXHR, textStatus, errorThrown)
            }
        },
        buttons: [
         'selectAll',
         'selectNone'
         ],
         language: {
             buttons: {
                 selectAll: "Select all items",
                 selectNone: "Select none"
             }
         },
         select: {
            selector: 'td:first-child',
            style: 'multi'
        },
        order: [0, 'desc'],
        scrollCollapse: true,
        scrollX: false,
        aLengthMenu: [
            [10, 25, 50, 100, -1],
            [10, 25, 50, 100, "All"]
        ],
        iDisplayLength: 25,
        "columns": [
            { "data": "EVENT_ID", "defaultContent": "", "sClass": "text-center limitedWidth" },
            { "data": "FLOW_ID", "defaultContent": "", "sClass": "text-center limitedWidth" },
            { "data": "FLOW_NAME", "defaultContent": "", "sClass": "text-center limitedWidthFlNm" },
            { "data": "OBJECT_TYPE", "defaultContent": "", "sClass": "text-center limitedWidth" },
            { "data": "OBJECT_NAME", "defaultContent": "", "sClass": "text-center limitedWidth" },
            { "data": "OBJECT_VERSION", "defaultContent": "", "sClass": "text-center limitedWidth" },
            { "data": "OBJECT_ITERATION", "defaultContent": "", "sClass": "text-center limitedWidth" },
            { "data": "OPERATION_NAME", "defaultContent": "", "sClass": "text-center limitedWidth" },
            { "data": "OPERATION_STATUS", "defaultContent": "", "sClass": "text-center limitedWidth" },
            { "data": "OPERATION_MESSAGE", "defaultContent": "", "sClass": "text-center limitedWidth" },
            { "data": "CREATE_DATE", "defaultContent": "", "sClass": "text-center limitedWidth","sType":"date" },
            { "data": "UPDATE_DATE", "defaultContent": "", "sClass": "text-center limitedWidth " ,"sType":"date" }
        ]
    })
    

    This is the script import :

    <!-- jQuery -->
    <script th:src="@{/vendor/jquery/jquery.min.js}"></script>
    
    <!-- Bootstrap Core JavaScript -->
    <script th:src="@{/vendor/bootstrap/js/bootstrap.min.js}"></script>
    
    <!-- Metis Menu Plugin JavaScript -->
    <script th:src="@{/vendor/metisMenu/metisMenu.min.js}"></script>
    
    <!-- DataTables JavaScript -->
    <script th:src="@{/vendor/datatables/js/jquery.dataTables.min.js}"></script>
    <script th:src="@{/vendor/datatables-plugins/dataTables.bootstrap.min.js}"></script>
    <script th:src="@{/vendor/datatables-responsive/dataTables.responsive.js}"></script>
    <script th:src="@{/vendor/datatables/js/dataTables.select.min.js}"></script>
    
    <script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.8.4/moment.min.js"></script>
    <script src="https://cdn.datatables.net/plug-ins/1.10.19/sorting/datetime-moment.js"></script>
    

    Did I do something wrong in use ?

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    Answer ✓

    Uncaught TypeError: Cannot read property 'length' of null

    My guess is with something in the data being returned from the Ajax request. Not sure though.

    Not that this will fix the problem but I don't think your datetime format is correct for moment.js. This [doc[(http://momentjs.com/docs/#/displaying/) contains the format strings supported. At least for the data I think you will want YYYY-MM-DD. Didn't check the time :smile:

    Another option would be to use [Orthogonal Data]((https://datatables.net/manual/data/orthogonal-data) and convert the datetime to a timestamp for the sort type.

    Kevin

  • MajesticMajestic Posts: 14Questions: 7Answers: 0

    You were right Kevin, something was wrong with the data. On java side, I've made a mistake on format, I wrote :
    -YYYY-MM-DD HH:mm:ss:SSS
    instead of
    -YYYY-MM-DD HH:mm:ss.SSS (colon instead of dot between ss and SSS).

    The plugin and the sort are working now ! Thank you for your help Kevin. Awesome !

This discussion has been closed.