ColumnDef

skolbaek

Hello :-)

My first column [0] contains data from a datetime column in MS SQL, and when not using datatables the order is correct, but when I use it in datatables it "out of the box" does not work as it should .. I have because of that tried to add columnDefs { "type": "datetime", "targets": 0 } like in below code but that does not work, the order is not correct:

$(document).ready(function () {

        $('#dtBasicExample').dataTable( {
          "language": {
              "url": "//cdn.datatables.net/plug-ins/9dcbecd42ad/i18n/Danish.json"
          },
          "columnDefs": [
            { "type": "datetime", "targets": 0 }
          ]
        } );

      $('.dataTables_length').addClass('bs-select');
      
});

The output in my SQL is i.e. : 2021-06-06 21:13:17.177

And the out put in ASP and how it should be is : 06-06-2021 21:13:17

Anyone who can see what is wrong?

If you need further info please do not hesitate to ask :-)

rf1234

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

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

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

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

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

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

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

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

No, I probably do not :-) .. Thank you for your effort though

kthorngren

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