Solved

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

Posted on 2013-01-10
22
17,676 Views
Last Modified: 2013-02-26
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..
0
Comment
Question by:kcmovies
  • 12
  • 10
22 Comments
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 38765631
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"},
            ],
0
 

Author Comment

by:kcmovies
ID: 38765759
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
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 38765772
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.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:kcmovies
ID: 38765890
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 ?
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 38765924
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?
0
 

Author Comment

by:kcmovies
ID: 38767349
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
0
 

Author Comment

by:kcmovies
ID: 38768112
I've verified my code, i am using only jquery.dataTables.js
0
 

Author Comment

by:kcmovies
ID: 38773228
Can anyone help on this issue please ?
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 38773238
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
0
 

Author Comment

by:kcmovies
ID: 38774678
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 ?
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 38774749
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.
0
 

Author Comment

by:kcmovies
ID: 38774994
Thank you. In jsbin.com i didn't find option to add our own js or css files ? I see option to add libraries.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 38775098
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.
0
 

Author Comment

by:kcmovies
ID: 38780171
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

0
 

Author Comment

by:kcmovies
ID: 38782674
Can anyone chek this please ?
0
 

Author Comment

by:kcmovies
ID: 38783012
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

0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 38783142
Please view source, copy and paste that here.
0
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 500 total points
ID: 38783185
Try and replace your js you last posted with below. I found errors and fixed.


$(document).ready(function() {

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));
};

		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

0
 

Author Comment

by:kcmovies
ID: 38783404
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

0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 38783475
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.
0
 

Author Comment

by:kcmovies
ID: 38783635
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.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 38783766
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.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A frequently asked question goes something like this:  "I am running a long process in the background and I want to alert my client when the process finishes.  How can I send a message to the browser?"  Unfortunately, the short answer …
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to implement Singleton Design Pattern in Java.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question