Solved

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

Posted on 2013-01-10
22
16,326 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:kcmovies
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I've verified my code, i am using only jquery.dataTables.js
0
 

Author Comment

by:kcmovies
Comment Utility
Can anyone help on this issue please ?
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:kcmovies
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Can anyone chek this please ?
0
 

Author Comment

by:kcmovies
Comment Utility
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
Comment Utility
Please view source, copy and paste that here.
0
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Graphical performance problems on Raspbian OS 2 65
mockito example issue 8 30
gif overlay 3 32
Cordova Camera plugin fails 2 12
Introduction JSON is an acronym for JavaScript Object Notation.  It is a text-string data transport mechanism, capable of representing simple or complex data structures in a consistent and easy-to-read manner.  Similar in concept to XML, but more e…
JavaScript can be used in a browser to change parts of a webpage dynamically. It begins with the following pattern: If condition W is true, do thing X to target Y after event Z. Below are some tips and tricks to help you get started with JavaScript …
The viewer will learn how to dynamically set the form action using jQuery.
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now