DataTables

jd-webdesign

Hi,

I have a table with 4 columns. The last one contains date (dd/mm/yyyy format).

But the date doesn't sort "properly". Can anyone help me ? (i'm completely new to javascript).

Here's a part of my code :

<link href="table/css/style.css" rel="stylesheet" media="all" type="text/css">
<script type="text/javascript" language="javascript" src="libraries/jquery/jquery.js"></script>
<script type="text/javascript" language="javascript" src="libraries/jquery/jquery.dataTables.min.js"></script>
<script type="text/javascript" charset="utf-8">
$(document).ready(function() {
	$('#mytable').dataTable( {
                "bJQueryUI": true,
	        "oLanguage": {
			"sUrl": "table/language/dataTables.french.txt"
			     },
                "sPaginationType": "full_numbers",
                "aoColumns": [
                              {"bSortable": true},
                              {"bSortable": false},
                              null,
                              null
                             ]
	} );
} );
</script>
<table id="mytable" class="display"> 
<thead>
<tr>
<th class="border-left">#</th>
<th>Ecouter</th>
<th>Nom</th>
<th class="border-right">Date</th>
</tr>
</thead>
<tbody>
<tr class="border-top">
<td>1</td>
<td>xxxxxxxxxxxxxxxxxxxxxxxxxx</td>
<td>xxxxxxxxxxxxxxxxxxxxxxxxxx</td>
<td>01/09/2010</td>
</tr>

What I have to add for sorting date and where ?

In advance thanks,

JD

deus_pater

To solve this problem, I added a hidden column in YYYYMMDD format, and pointed my DD/MM/YYYY date column to sort on the hidden column.

So, from your example above:

<script type="text/javascript" charset="utf-8">
$(document).ready(function() {
	$('#podcastscitematin').dataTable( {
                "bJQueryUI": true,
	        "oLanguage": {
			"sUrl": "table/language/dataTables.french.txt"
			     },
                "sPaginationType": "full_numbers",
                "aoColumns": [
                              {"bSortable": true},
                              {"bSortable": false},
                              null,
                              {"iDataSort": 4},
                              {"bVisible": false}
                             ]
	} );
} );
</script>
</head>
<table id="mytable" class="display"> 
<thead>
<tr>
<th class="border-left">#</th>
<th>Ecouter</th>
<th>Nom</th>
<th class="border-right">Date</th>
<th>Sortable Date</th>
</tr>
</thead>
<tbody>
<tr class="border-top">
<td>1</td>
<td>xxxxxxxxxxxxxxxxxxxxxxxxxx</td>
<td>xxxxxxxxxxxxxxxxxxxxxxxxxx</td>
<td>01/09/2010</td>
<td>20100901</td>
</tr>

There may be better ways, but this worked for me.

Hope that helps,
Mayank

jd-webdesign

Hi,

Thanks a lot for your time.

I think your idea is good and works but maybe a little bit "heavy" to do if the table have a lot of datas.

If anyone have an another solution it could be so cool.

Anyway thanks a lot deus_pater

deus_pater

There's more discussion of this topic here:
http://datatables.net/forums/comments.php?DiscussionID=1952&page=1

Including how to write a custom sort function.

Hope that helps!

jd-webdesign

Hi thanks again,

I think I have to add a plug-in like here :

http://datatables.net/plug-ins/sorting

But I'm not sure how to add this to my code ?

JD

jd-webdesign

Hi,

I try this but it doesn't work :

HTML code :

<link href="table/theme/style.css" rel="stylesheet" media="all" type="text/css">
<script type="text/javascript" language="javascript" src="libraries/jquery/jquery.js"></script>
<script type="text/javascript" language="javascript" src="libraries/jquery/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="libraries/jquery/dataTables.date.js"></script>
<script type="text/javascript" charset="utf-8">
  $(document).ready(function() {
    $('#mytable').dataTable( {
      "bJQueryUI": true,
      "oLanguage": {
        "sUrl": "table/language/dataTables.french.txt"
      },
      "sPaginationType": "full_numbers",
      "aoColumns": [
        {"bSortable": true},
        {"bSortable": false},
        null,
        {"sType": "date-euro"},
        null
      ]
    } );
  } );
</script>
<table id="mytable" class="display"> 
<thead>
<tr>
<th class="border-left">#</th>
<th>Ecouter</th>
<th>Nom</th>
<th class="border-right">Date</th>
</tr>
</thead>
<tbody>
<tr class="border-top">
<td>1</td>
<td>xxxxxxxxxxxxxxxxxxxxxxxxxx</td>
<td>xxxxxxxxxxxxxxxxxxxxxxxxxx</td>
<td>01/09/2010</td>

I add the plugin from Ronan Guilloux found on this page http://www.datatables.net/plug-ins/sorting but the sorting doesn't work.

The JS code :

function trim(str) {
	str = str.replace(/^\s+/, '');
	for (var i = str.length - 1; i >= 0; i--) {
		if (/\S/.test(str.charAt(i))) {
			str = str.substring(0, i + 1);
			break;
		}
	}
	return str;
}

jQuery.fn.dataTableExt.oSort['date-euro-asc'] = function(a, b) {
	if (trim(a) != '') {
		var frDatea = trim(a).split(' ');
		var frTimea = frDatea[1].split(':');
		var frDatea2 = frDatea[0].split('/');
		var x = (frDatea2[2] + frDatea2[1] + frDatea2[0] + frTimea[0] + frTimea[1] + frTimea[2]) * 1;
	} else {
		var x = 10000000000000; // = l'an 1000 ...
	}

	if (trim(b) != '') {
		var frDateb = trim(b).split(' ');
		var frTimeb = frDateb[1].split(':');
		frDateb = frDateb[0].split('/');
		var y = (frDateb[2] + frDateb[1] + frDateb[0] + frTimeb[0] + frTimeb[1] + frTimeb[2]) * 1;		                
	} else {
		var y = 10000000000000;		                
	}
	var z = ((x < y) ? -1 : ((x > y) ? 1 : 0));
	return z;
};

jQuery.fn.dataTableExt.oSort['date-euro-desc'] = function(a, b) {
	if (trim(a) != '') {
		var frDatea = trim(a).split(' ');
		var frTimea = frDatea[1].split(':');
		var frDatea2 = frDatea[0].split('/');
		var x = (frDatea2[2] + frDatea2[1] + frDatea2[0] + frTimea[0] + frTimea[1] + frTimea[2]) * 1;		                
	} else {
		var x = 10000000000000;		                
	}

	if (trim(b) != '') {
		var frDateb = trim(b).split(' ');
		var frTimeb = frDateb[1].split(':');
		frDateb = frDateb[0].split('/');
		var y = (frDateb[2] + frDateb[1] + frDateb[0] + frTimeb[0] + frTimeb[1] + frTimeb[2]) * 1;		                
	} else {
		var y = 10000000000000;		                
	}		            
	var z = ((x < y) ? 1 : ((x > y) ? -1 : 0));		            
	return z;
};

What I'm doing wrong ?

Thanks,

JD

deus_pater

Hi JD,

At the bottom of the plugins page, there's a "how to use" section -- I haven't tried it, but it looks like you might need to use "sSortDataType" set to "dom-text" in addition to "sType" in your columns spec.

Hope that helps,
Mayank

jd-webdesign

I found my problem it's just because I made a mistake with the placement of "sType" (not assigned to the right column).

Now everything works great and the sorting of my date column works like a charm:)

kkudi

go to datejs.com and download the javascript file from there.

it overrides Date.parse and it pretty much caters for all scenarios above.

Allan's code uses Date.parse to sort columns with sType date so it works pretty well.

Maybe not as fast as Date.parse native function but still does the trick. Otherwise just a format which is parsable by all browsers.

PaoloValladolid

datejs.com is noticeably slower than the default Date.parse. OTOH, it gets the dates sorted correctly.

ComboFusion

I would like to add that the plugin mentioned by jd-webdesign DOES NOT sort date-time properly.

melanke

i`ve made a plugin for sorting date dd/mm/yyyy hh:mm
and it works properly


function trim(str) {
                        str = str.replace(/^\s+/, '');
                        for (var i = str.length - 1; i >= 0; i--) {
                                if (/\S/.test(str.charAt(i))) {
                                        str = str.substring(0, i + 1);
                                        break;
                                }
                        }
                        return str;
                }

function dateHeight(dateStr){
        if (trim(dateStr) != '') {
                var frDate = trim(dateStr).split(' ');
                var frTime = frDate[1].split(':');
                var frDateParts = frDate[0].split('/');
                var day = frDateParts[0] * 60 * 24;
                var month = frDateParts[1] * 60 * 24 * 31;
                var year = frDateParts[2] * 60 * 24 * 366;
                var hour = frTime[0] * 60;
                var minutes = frTime[1];
                var x = day+month+year+hour+minutes;
        } else {
                var x = 99999999999999999; //GoHorse!
        }
        return x;
}

                jQuery.fn.dataTableExt.oSort['date-euro-asc'] = function(a, b) {
                        var x = dateHeight(a);
                        var y = dateHeight(b);
                        var z = ((x < y) ? -1 : ((x > y) ? 1 : 0));
                        return z;
                };

                jQuery.fn.dataTableExt.oSort['date-euro-desc'] = function(a, b) {
                        var x = dateHeight(a);
                        var y = dateHeight(b);
                        var z = ((x < y) ? 1 : ((x > y) ? -1 : 0));
                        return z;
                };

                $(function(){

                    $("table").dataTable({
                        "aoColumns": [
                                { "sType": "date-euro"},
                                null,
                                null,
                                null,
                                null
                        ]
                    });

                });

gsgill76

I had same issue of sorting (US-Style DateTime Field)

deus_pater

you solve my issue.... Thanks a-lot

Ashishus

deus_pater

Please explain me in details , how to use hidden column and sort the corresponding date column .

Danny

Thank's melanke. It run ok, but I have a column with dontSort and now it doesn't work.

Danny

OK, I have a solution if you need a noSorts columns and a colum with sorting date dd/mm/yyyy hh:mm.
You need melanke's code and this:

         var dontSort = [];
         var id="#large";

            $(id+' thead th').each( function () {
                if ( $(this).hasClass( 'no_sort' )) {
                    dontSort.push( { "bSortable": false } );
                } else {
                    if($(this).hasClass( 'date' )){
                        dontSort.push({ "sType": "date-euro"});
                    }
                    else{
                        dontSort.push( null );
                    }
                }
            } );
                $(id).dataTable( {
                    "aoColumns": dontSort
                 } );

//Now in thead you can write two class (no_sort or date)
<th class="no_sort"></th>
                <th >field1</th>
                <th  >field2</th>
                <th class="date">Fecha</th>

Gangadhar

Thanks "melanke" for your code.

jheaddon

Hi guys,

Any chance somebody could modify the to support columns in the format 'ddd dd/MM/yy HH:mm:ss' without the use of a hidden sort column?

For example:

'Mon 20/01/12 20:20:20'

Cheers!

jheaddon

Nevermind I figured it out, for anyone interested you need to use melanke's code above and change the following function:

// Splits the provided input string in to format dd/MM/yy HH:mm:ss
function dateHeight(inputString)
{
var result = 99999999999999999;

if (trim(inputString) != '')
{
var frDate = trim(inputString).split(' ');
var frTime = frDate[2].split(':');
var frDateParts = frDate[1].split('/');
var day = frDateParts[0] * 60 * 24;
var month = frDateParts[1] * 60 * 24 * 31;
var year = frDateParts[2] * 60 * 24 * 366;
var hour = frTime[0] * 60;
var minutes = frTime[1];
var seconds = frTime[2];
result = day + month + year + hour + minutes + seconds;
}

return result;
};

DataTables

Howdy, Stranger!

Support

Categories

In this Discussion