Link to home
Start Free TrialLog in
Avatar of kcmovies
kcmovies

asked on

JQuery datatable date(mm/dd/YYY) sort issue

Hi Experts,

I am using below JQuery datatable code to display data on JSP page. Date sorting is not working properly. From the forums (sample plug in code) what i understand was i need to use some plug in to get this date sort functionality in desired format (mm/dd/YYY). I am not sure how to embed the plugin in my current datatable code. Please help me. I am not expert in using JQuery.

My datatable code :
function initDisplayTable(){
	oTable = $('#displayData').dataTable( {
		"aLengthMenu": [[25, 50, 100, 200], [25, 50, 100, 200]],
		"bFilter": false,
		"iDisplayLength": 25,
		"bProcessing": true,
		"bStateSave": true,
		"bServerSide": true,
		"bAutoWidth": false,
		"bRetrieve": true,
		"sAjaxSource": document.getElementById('hdnURL').value,
		"aoColumns": [ 
			{"sName": "id", "sTitle": "", "bSortable": "true"},
			{"sName": "refID", "sTitle": "Corr ID", "bSortable": "true"},
			{"sName": "dateReceived", "sTitle": "Date Received", "bSortable": "true"},
		],
		"sPaginationType": "full_numbers",
		"aaSorting": [[0,'asc']],
		"oLanguage": {
		"sLengthMenu": "Page length: _MENU_",
		"sSearch": "Filter:",
		"sZeroRecords": "No records found"
		     },
		"fnServerData": function ( sSource, aoData, fnCallback){},			     
		"fnServerParams": function ( aoData ) {}		    
	});
}

Open in new window


Plugin Code got from forums:

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

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

Open in new window


Thanks in advance..
Avatar of Scott Fell
Scott Fell
Flag of United States of America image

You need to define the column as a date with  "sType": "date"


"aoColumns": [
                  {"sName": "id", "sTitle": "", "bSortable": "true"},
                  {"sName": "refID", "sTitle": "Corr ID", "bSortable": "true"},
                  {"sName": "dateReceived", "sTitle": "Date Received", "sType": "date" , "bSortable": "true"},
            ],
Avatar of kcmovies
kcmovies

ASKER

Thanks for your reply. Looks like no change in order even i add "sType", please see below sorted dates. Looks like it is sorting only by uisng first two numbers in date.

1/8/2013
1/8/2013
1/8/2013
1/8/2013
1/8/2013
11/16/2012
11/27/2012
11/27/2012
11/27/2012
11/27/2012
11/27/2012
11/27/2012
11/27/2012
11/29/2012
11/29/2012
11/29/2012
11/29/2012
11/29/2012
11/29/2012
11/29/2012
11/29/2012
12/3/2012
12/3/2012
You have "aaSorting": [[0,'asc']],  meaning the default sort is on the 1st column.  Try using "aaSorting": [[2,'asc']], which is the 3rd column.

Just to confirm - you have 3 columns of data?

Then just for testing change

 {"sName": "dateReceived", "sTitle": "Date Received", "sType": "date" , "bSortable": "true"},

to

 {"sType": "date" },

If that works then add back the rest.  

Also using your browser console (firebug or chrome or safari see if you have errors popping up.   The smallest error will prevent this stuff from working.
Thanks for your time.

When i use "aaSorting": [[2,'asc']],", by default it is selecting that column, but sort is not correct.

Totally i've 8 columns of data, in those two are date columns.

Even i use " {"sType": "date" }," alone, sort issue is same.

I've checked in firebug and chore, no errors were popped up.

Please let me if know any other work arounds ?
If you have 8 columns of data, I think you should have 8 entries in aoColumns and not 3.  Also the comma at the last curly bracket probably should not be there.  

"aoColumns": [
                  {"sName": "id", "sTitle": "", "bSortable": "true"},
                  {"sName": "refID", "sTitle": "Corr ID", "bSortable": "true"},
                  {"sName": "dateReceived", "sTitle": "Date Received", "bSortable": "true"},
            ],

change
{"sName": "dateReceived", "sTitle": "Date Received", "bSortable": "true"},
to
{"sName": "dateReceived", "sTitle": "Date Received", "bSortable": "true"}

aoColumns should be

"aoColumns": [
                  {"sName": "id", "sTitle": "", "bSortable": "true"},
                  {"sName": "refID", "sTitle": "Corr ID", "bSortable": "true"},
                  {"sName": "dateReceived", "sTitle": "Date Received", "bSortable": "true"},
{ "bSortable": "true"},
{ "bSortable": "true"},
{ "bSortable": "true"},
{ "bSortable": "true"},
{ "bSortable": "true"}

            ],

Are you sure datatables.js is bering called?
Sorry i did not mention this before. I did setup in the following manner, but for simplicity in post i've shown only 3 columns.

aoColumns": [
                  {"sName": "id", "sTitle": "", "bSortable": "true"},
                  {"sName": "refID", "sTitle": "Corr ID", "bSortable": "true"},
                  {"sName": "dateReceived", "sTitle": "Date Received", "bSortable": "true"},
{ "bSortable": "true"},
{ "bSortable": "true"},
{ "bSortable": "true"},
{ "bSortable": "true"},
{ "bSortable": "true"}
 ]

Currently i've following files in my directory. I am not sure which one is being used. You want me to try remove one by one and see ?
jquery.dataTables.js
jquery.dataTables.min.js
I've verified my code, i am using only jquery.dataTables.js
Can anyone help on this issue please ?
Can you please post a link to your page or throw the entire page html with full links to your js and css in jsbin.com
I am dispyaing JSP page in websphere portal application. This is internal application, i am not sure how to provide link to view.
I never used jsbin, please let me know how can i post HTML, CSS and JS files ?

I can open page in my local work station, and i can get HTML from view source and attach here, is this something helpfull to you to check ?
Can you view source - make that to a static html page, post that to your server and lets work on it statically.    If you post to jsbin.com you just paste in your html  portion in the html area and include any full links to javascript files and css files just as you do on your page.  Then in the css area add your css and add your javascript that is on page in the javascript area.
Thank you. In jsbin.com i didn't find option to add our own js or css files ? I see option to add libraries.
I created a jsbin from their sample.  http://jsbin.com/urupuk/1/edit

This should get you started.  fyi you can close and open the javascript, css ,  html and output area if that helps give you more room in one spot.

Click on html to view the html and remove the table/div data I have and replace with your own.  Then check out the js by clicking on the javascript and replace what is there with what you have.

I probably will not be able to check this again for a few hours.
Sorry for very late reply. I was caught up for some other issue. Again i am back to this issue.

The above link is not working (Error 503 Service Unavailable), mine is portal application, i've lot of .js and .css in my work space. Looks like using jsbin is time taking. I am not sure that jsbin have any more advantages than chrome develeper tool.

I did some more reaseach using chrome. I see all the loaded .js files. In those i see jquery.datatables.js got loaded. I put a alert in below code (in jquery.datatables.js), when i click sort on data table date field, the below fucntion not being called. I am thiking that might be the reason it is not working. Can you please help ?

	/*
	 * Variable: oSort
	 * Purpose:  Wrapper for the sorting functions that can be used in DataTables
	 * Scope:    jQuery.fn.dataTableExt
	 * Notes:    The functions provided in this object are basically standard javascript sort
	 *   functions - they expect two inputs which they then compare and then return a priority
	 *   result. For each sort method added, two functions need to be defined, an ascending sort and
	 *   a descending sort.
	 */
	_oExt.oSort = {
                     ***********removed code for similicity*******
		/*
		 * date sorting
		 */
		"date-asc": function ( a, b )
		{
			alert("dateasc")
			var x = Date.parse( a );
			var y = Date.parse( b );
			
			if ( isNaN(x) || x==="" )
			{
			x = Date.parse( "01/01/1970 00:00:00" );
			}
			if ( isNaN(y) || y==="" )
			{
				y =	Date.parse( "01/01/1970 00:00:00" );
			}
			
			return x - y;
		},
		
		"date-desc": function ( a, b )
		{
			var x = Date.parse( a );
			var y = Date.parse( b );
			
			if ( isNaN(x) || x==="" )
			{
			x = Date.parse( "01/01/1970 00:00:00" );
			}
			if ( isNaN(y) || y==="" )
			{
				y =	Date.parse( "01/01/1970 00:00:00" );
			}
			
			return y - x;
		}

Open in new window

Can anyone chek this please ?
I tried one more option to insert pluig in working .js file. But it is not being called when i click sort. I am not sure why it is not calling. I tried this portion of plugin in  jsfiddle.net, its works fine, but its not working in my below code. Please verify and let me know if i am doing any thing wrong. Or please let me know how can i figure out why plugin function is not being called.

jQuery.fn.dataTableExt.oSort['usdate-asc']  = function(a,b) {
	alert('sadasda');
    var usDatea = $(a).text().split('/'); 
    var usDateb = $(b).text().split('/'); 

    var x = (usDatea[2] + usDatea[0] + usDatea[1]) * 1;
    var y = (usDateb[2] + usDateb[0] + usDateb[1]) * 1;

    return ((x < y) ? -1 : ((x > y) ?  1 : 0));
};

jQuery.fn.dataTableExt.oSort['usdate-desc'] = function(a,b) {
    var usDatea = $(a).text().split('/'); 
    var usDateb = $(b).text().split('/'); 

    var x = (usDatea[2] + usDatea[0] + usDatea[1]) * 1;
    var y = (usDateb[2] + usDateb[0] + usDateb[1]) * 1;

    return ((x < y) ? 1 : ((x > y) ?  -1 : 0));
$(document).ready(function(){
		function initDisplayTable(){
		    oTable = $('#displayData').dataTable( {
				"aLengthMenu": [[25, 50, 100, 200], [25, 50, 100, 200]],
				"bFilter": false,
				"iDisplayLength": 25,
			    "bProcessing": true,
			    "bStateSave": true,
			    "bServerSide": true,
			    "bAutoWidth": false,
			    "bRetrieve": true,
			    "sAjaxSource": document.getElementById('hdnGenResourceURL').value,
			    "aoColumns": [ 
					{"sName": "id", "sTitle": "", "bSortable": "true"},
					{"sName": "refID", "sTitle": "Corr ID/SR ID/Doc ID", "bSortable": "true", "sType":"numeric"},
					{"sName": "numOfInvoices", "sTitle": "# of Invoices", "bSortable": "true"},
					{"sName": "dateReceived", "sTitle": "Date Received", "sType" : "usdate" , "bSortable": "true"},
					{"sName": "respDueDate", "sTitle": "Response Due Date", "bSortable": "true"},
					{"sName": "modeOfReceipt", "sTitle": "Mode of Receipt", "bSortable": "true"},
					{"sName": "dispWorker", "sTitle": "Dispute Worker Assigned", "bSortable": "true"},
					{"sName": "dispWorkerRacf", "sTitle": "", "bSearchable": false, "bVisible": false},
					{"sName": "InitialState", "sTitle": "", "bSearchable": false, "bVisible": false},
					{"sName": "BillingAuthority", "sTitle": "", "bSearchable": false, "bVisible": false},
					{"sName": "CorrId", "sTitle": "", "bSearchable": false, "bVisible": false},
					{"sName": "ProcessId", "sTitle": "", "bSearchable": false, "bVisible": false}
					],
				"sPaginationType": "full_numbers",
				"aaSorting": [[3,'asc']],
				"oLanguage": {
			        "sLengthMenu": "Page length: _MENU_",
			        "sSearch": "Filter:",
			        "sZeroRecords": "No records found"
			     }

}
};

Open in new window

Please view source, copy and paste that here.
ASKER CERTIFIED SOLUTION
Avatar of Scott Fell
Scott Fell
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you. Looks like you added "});", i think it is there in my code.  I did not added that part in my previous post. Please review this.

jQuery.fn.dataTableExt.oSort['usdate-asc']  = function(a,b) {
	alert('sadasda');
    var usDatea = $(a).text().split('/'); 
    var usDateb = $(b).text().split('/'); 

    var x = (usDatea[2] + usDatea[0] + usDatea[1]) * 1;
    var y = (usDateb[2] + usDateb[0] + usDateb[1]) * 1;

    return ((x < y) ? -1 : ((x > y) ?  1 : 0));
};

jQuery.fn.dataTableExt.oSort['usdate-desc'] = function(a,b) {
    var usDatea = $(a).text().split('/'); 
    var usDateb = $(b).text().split('/'); 

    var x = (usDatea[2] + usDatea[0] + usDatea[1]) * 1;
    var y = (usDateb[2] + usDateb[0] + usDateb[1]) * 1;

    return ((x < y) ? 1 : ((x > y) ?  -1 : 0));
};

$(document).ready(function(){
		function initDisplayTable(){
		    oTable = $('#displayData').dataTable( {
				"aLengthMenu": [[25, 50, 100, 200], [25, 50, 100, 200]],
				"bFilter": false,
				"iDisplayLength": 25,
			    "bProcessing": true,
			    "bStateSave": true,
			    "bServerSide": true,
			    "bAutoWidth": false,
			    "bRetrieve": true,
			    "sAjaxSource": document.getElementById('hdnGenResourceURL').value,
			    "aoColumns": [ 
					{"sName": "id", "sTitle": "", "bSortable": "true"},
					{"sName": "refID", "sTitle": "Corr ID/SR ID/Doc ID", "bSortable": "true", "sType":"numeric"},
					{"sName": "numOfInvoices", "sTitle": "# of Invoices", "bSortable": "true"},
					{"sName": "dateReceived", "sTitle": "Date Received", "sType" : "usdate" , "bSortable": "true"},
					{"sName": "respDueDate", "sTitle": "Response Due Date", "bSortable": "true"},
					{"sName": "modeOfReceipt", "sTitle": "Mode of Receipt", "bSortable": "true"},
					{"sName": "dispWorker", "sTitle": "Dispute Worker Assigned", "bSortable": "true"},
					{"sName": "dispWorkerRacf", "sTitle": "", "bSearchable": false, "bVisible": false},
					{"sName": "InitialState", "sTitle": "", "bSearchable": false, "bVisible": false},
					{"sName": "BillingAuthority", "sTitle": "", "bSearchable": false, "bVisible": false},
					{"sName": "CorrId", "sTitle": "", "bSearchable": false, "bVisible": false},
					{"sName": "ProcessId", "sTitle": "", "bSearchable": false, "bVisible": false}
					],
				"sPaginationType": "full_numbers",
				"aaSorting": [[3,'asc']],
				"oLanguage": {
			        "sLengthMenu": "Page length: _MENU_",
			        "sSearch": "Filter:",
			        "sZeroRecords": "No records found"
			     },
				"fnServerData": function ( sSource, aoData, fnCallback){
					
			            $.ajax( {"dataType": 'json',
			                 "type": "GET", 
			                 "url": sSource,
			                 "data": aoData,
			                 "success": function(result){

			                 		errorArray = result['serverResponse'];
									var len = errorArray[2];
									var isError = errorArray[0];
									var msg = errorArray[1];
									if(len >= 1){
										$('#repopField').hide();
										$('#QFieldSet').show('slow');
										if(isError == true || isError == 'true'){
											showQMsgBox('error', msg);
										}
										if(isError == false || isError == 'false'){
											showQMsgBox('success',msg);			
										}								
									}else{
										if(varBtnFlag == 'search'){
											var msg = 'no data available, please click re-populate button ';
											showQMsgBox('warning',msg);
											$('#QFieldSet').hide();
											$('#repopField').show();
										}else{
											// on startup - do nothing
										}
									}
									resetBtnFlag();
			                 		fnCallback(result);
			                 	}
			                 } );
		         	},			     
		        "fnServerParams": function ( aoData ) {
		        	aoData.push( { "name": "fromID", "value": "ajaxDataTables" } );
		        	if(varBtnFlag == 'search'){
		        		var searchBy = $('#qSearchBy :selected').text();
		        		var searchText = $('#qsearchTXT').val();
		        		aoData.push( { "name": "callTypeFrom", "value": "qSearch" } );
		        		aoData.push( { "name": "searchBy", "value": searchBy } );
		        		aoData.push( { "name": "searchText", "value": searchText } );
		        	}else if(varBtnFlag == 'getNext'){
		        		aoData.push( { "name": "callTypeFrom", "value": "qGetNext" } );
		        	}else if(varBtnFlag == 'reAssign'){
		        		//var isDeptSel = validateAssign();
		           		//if(isDeptSel == true){
							var allOtherVals = fetchQVals();
							var allQVals = JSON.stringify(qValsMap);	
		        			aoData.push( { "name": "callTypeFrom", "value": "qReAssign" } );
		        			aoData.push( { "name": "allOtherVals", "value": allOtherVals } );
		        			aoData.push( { "name": "qData", "value": allQVals } );
		        		//}	
		        	}
			    }		    
		    });
        }
});

Open in new window

Look, I am trying to help you and you have to cooperate if you want help.  

AS I said, one small error causes everything to break.  This has become longer thread then it needs to be.  I asked to have all of your code together and that has not happened.  I just asked to have the complete html of your page and that has not happened.   When we ask for these types of things it is not to just to make more work for you but to help us, help you and get the full story of the problem.

 I have already debugged the js you posted and now you have updated code that has more errors.  Please remember we are doing this on a volunteer basis and really only supposed to help guide you and not write all of your code for you.

I will try one last time.   Please figure a way for me to see your full static code all working together.  Either post a link to a static version (no php/asp) of the html with all supporting js/css/image files OR get everything in something like jsbin or jsfiddle.
I am sorry to waste your time for small issue. I undertsand that you are tyring to help volunteer.

First thing is, i am new to front end stuff. Since it is portal application, when i try to get static code using view source i am seeing lot of code related to portal theme etc... Related to this issue I am seeing only table header. Poulating data is handling by java script. That is the reason i've posted related function in js file.

I've tried to set this up in jsbin, but i did not succeded to make it work. I will try again and let you know.
Part of  your learning will be to figure out how to get rendered html....  Either figure that out or make your page public where somebody can surf to it and see what is going on.