Date search does not always work
Date search does not always work
PaulVickery
Posts: 41Questions: 6Answers: 0
Good morning, please can someone assist me
I have two issues, however as I cannot demonstrate them properly on the live.datatables.net site, I have created a test site at https://dt.taskmgr.co.uk/ (I needed to show the issues using ajax and a date format of "2026-05-25", i.e. 'Y-m-d'.
I have not seen any recent issues reported in this area, so apologies if this is a duplicate.
Issue 1)
I cannot get search working correctly with columnControl. I am using moment.js and I can order the dates correctly. However certain dates cannot be searched.
In the example, I have provided four dates, two can be searched, and two cannot.
Issue 2)
When stateSave is used in conjunction with columnControl, then even fewer dates can be searched. In the example, I have provided two displays, one that shows what happens when stateSave is set to “true” and one when it is commented out.
To reproduce using Chrome using the test site:
1) Clear the browser cache
2) Reload the page (F5)
3) In the Date columnControl search box where stateSave set to true, search for the four dates in turn
- 25/05/26 should fail
- 06/02/26 should pass
- 29/11/26 should pass
- 26/04/26 should fail
4) Repeat step 3 using the bottom table and the results should be the same.
5) Reload the page
6) Search again for the two dates that passed in step 3, i.e. 06/02/26 and 29/11/26 in the top table. Both should now fail
7) However, if you check in the bottom table, where stateSave is commented out, the results should be the same as in step 3.
Issue 1 can also be seen when looking at the example page https://datatables.net/extensions/columncontrol/examples/searchDates/rendering-moment.html and searching for 7th June 2011 in the “Start date” search box.
Relevant files are:
<!doctype html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<meta name="viewport" content="width=device-width,initial-scale=1">
<title>Datetest</title>
<script src="https://code.jquery.com/jquery-3.7.1.js"></script>
<script src="https://cdn.datatables.net/2.3.7/js/dataTables.js"></script>
<script src="https://cdn.datatables.net/columncontrol/1.2.0/js/dataTables.columnControl.js"></script>
<script src="https://cdn.datatables.net/columncontrol/1.2.0/js/columnControl.dataTables.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.30.1/moment.min.js"></script>
<script src="https://cdn.datatables.net/datetime/1.6.3/js/dataTables.dateTime.min.js"></script>
<script src="https://cdn.datatables.net/buttons/3.2.6/js/dataTables.buttons.js"></script>
<script src="https://cdn.datatables.net/buttons/3.2.6/js/buttons.dataTables.js"></script>
<script src="https://cdn.datatables.net/select/3.1.3/js/dataTables.select.js"></script>
<script src="https://cdn.datatables.net/select/3.1.3/js/select.dataTables.js"></script>
<link rel="stylesheet" type="text/css" href="css/generator-base.css">
<link rel="stylesheet" type="text/css" href="css/editor.dataTables.min.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/2.3.7/css/dataTables.dataTables.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/columncontrol/1.2.0/css/columnControl.dataTables.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/datetime/1.6.3/css/dataTables.dateTime.min.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/3.2.6/css/buttons.dataTables.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/select/3.1.3/css/select.dataTables.css">
<script type="text/javascript" charset="utf-8" src="js/dataTables.editor.min.js"></script>
<script type="text/javascript" charset="utf-8" src="js/table.datetest.js"></script>
</head>
<body class="dataTables">
<div class="container">
<h2>stateSave set to true</h2>
<table cellpadding="0" cellspacing="0" border="0" class="display" id="datetest" width="100%">
<thead>
<tr>
<th>Name</th>
<th>Date</th>
</tr>
</thead>
</table>
<BR>
<hr>
<h2>stateSave commented out</h2>
<table cellpadding="0" cellspacing="0" border="0" class="display" id="datetest1" width="100%">
<thead>
<tr>
<th>Name</th>
<th>Date</th>
</tr>
</thead>
</table>
</div>
</body>
</html>
Javascript File
addEventListener("DOMContentLoaded", function () {
var editor = new DataTable.Editor({
ajax: 'php/table.datetest.php',
table: '#datetest',
fields: [
{
"label": "Name:",
"name": "name"
},
{
"label": "Date:",
"name": "mydate",
"type": "datetime",
"format": "DD\/MM\/YY",
displayFormat: 'DD\/MM\/YY',
wireFormat: 'YYYY-MM-DD',
}
]
});
var table = new DataTable('#datetest', {
ajax: 'php/table.datetest.php',
columnControl: [
{
target: 0,
content: ['orderStatus']
},
{
target: 1,
content: ['search']
}
],
stateSave: true,
ordering: {
indicators: false
},
columns: [
{
"data": "name"
},
{
"data": "mydate"
}
],
columnDefs: [
{
targets: [1],
render: DataTable.render.datetime('DD\/MM\/YY'),
}
],
layout: {
topStart: {
buttons: [
{ extend: 'create', editor: editor },
{ extend: 'edit', editor: editor },
{ extend: 'remove', editor: editor }
]
},
bottomStart: null,
bottomEnd: null
},
select: true
});
var table = new DataTable('#datetest1', {
ajax: 'php/table.datetest.php',
columnControl: [
{
target: 0,
content: ['orderStatus']
},
{
target: 1,
content: ['search']
}
],
// stateSave: true,
ordering: {
indicators: false
},
columns: [
{
"data": "name"
},
{
"data": "mydate"
}
],
columnDefs: [
{
targets: [1],
render: DataTable.render.datetime('DD\/MM\/YY'),
}
],
layout: {
topStart: null,
bottomStart: null,
bottomEnd: null
},
select: true
});
});
PHP Controller file
<?php
include( "lib/DataTables.php" );
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate,
DataTables\Editor\ValidateOptions;
$db->sql( "CREATE TABLE IF NOT EXISTS `datetest` (
`id` int(10) NOT NULL auto_increment,
`name` varchar(255),
`mydate` date,
PRIMARY KEY( `id` )
);" );
Editor::inst( $db, 'datetest', 'id' )
->fields(
Field::inst( 'name' ),
Field::inst( 'mydate' )
->validator( Validate::dateFormat( 'Y-m-d' ) )
->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
->setFormatter( Format::dateFormatToSql( 'Y-m-d' ) )
)
->process( $_POST )
->json();
Example data showing date format
{
"data": [
{
"DT_RowId": "row_1",
"name": "user1",
"mydate": "2026-05-25"
},
{
"DT_RowId": "row_2",
"name": "user2",
"mydate": "2026-02-06"
},
{
"DT_RowId": "row_3",
"name": "user3",
"mydate": "2026-11-29"
},
{
"DT_RowId": "row_4",
"name": "user4",
"mydate": "2026-04-26"
}
],
"options": [],
"files": []
}
This question has an accepted answers - jump to answer
Answers
What an excellent bug report - thank you! I have actually seen this issue reported before, but never so well, and in a stroke of good timing, it was on my list for today to try and address
.
I've made a couple of commits to ColumnControl to fix the issue and I wonder if you could use the nightly version of ColumnControl's JS to check that it addresses the problem in your test case please?
Many thanks,
Allan
Hi Alan, thank you looking at this so quickly. I will check it again tomorrow with the nightly version and report back.
Just checking, but does this also address the stateSave issue?
Sorry to ask, but which of these should I swap out for the nightly
dataTables.columnControl.js
columnControl.dataTables.js
regards
Paul
Just
).
dataTables.columnControl.jswill do - with this file (include the query parameter to make sure any edge cache gets "busted"I am tracing an issue with dates and state saving at the moment, which is proving to be frustratingly difficult to resolve cleanly!
Allan
Updated nightly now has the fix for the date / state save issue as well.
Allan
Hi Alan
I swapped out the
dataTables.columnControl.jsfile for the nightly one.The two issues that I mentioned have been resolved, thank you. However, I have now noticed another issue which I did not see previously.
To reproduce:
The two dates behave differently, however please note that this time the other two dates in the example both behave the same as (25/05/26) .
I have done some further tests today to test this latest issue and I have found the following.
Item 3 above has now changed in that "Leaving the date (06/02/26) in the search box, refresh the page" you now get the row returned, i.e. working correctly. (not sure how that changed from yesterday)
However, I have noticed that if the day is between 1 and 12, then it appears to work ok, but if the day is 13 or more all dates are displayed on page refresh
That's weird!!! I'll try to take a look at it tomorrow. Thanks for digging into it a bit more.
Allan
Sorry, it hasn't happened today - got wrapped up in documentation updates. Will aim for tomorrow.
Allan
Thanks Alan, no hurry it's not that serious
Hi Alan, any idea when the fix for the first set of issues will be in the release version?
Probably in a week or two. I'm hoping to circle back to this after the current chunk I'm working on is wrapped up. Apologies for the delay.
Allan
Thx very much
Hello,
I have a similar problem with the ColumnControl component.
If someone could take a look and tell me what's wrong, that would be very kind.
Thank you in advance for your help.
The post where you will find the test case:
https://datatables.net/forums/discussion/81492/unable-to-find-an-exact-date-using-fixedcolumns-component-and-calendar-with-data-in-datetime-format
Hi Alan
I appreciate you are full on with V3, but any chance that you can get this resolved.
Regards
Paul
Yes, sorry! I've been completely consumed by trying to get everything ready for DT3, and this has slipped, apologies. I've got a few other commitments today, but I will try to look at it this afternoon, if not, then Monday.
Allan
No problem, just wanted to make sure it didn't fall through the cracks.
@allan,
Thank you in advance for taking the time to look into this issue, as it makes the calendar virtually unusable for dates in French.
Hi both,
Many thanks for your patience. I've just recreated the original page with the current nightly builds and followed the steps from this specific comment and it appears to be working with the latest software:
Could one or both of you confirm that it is working for you with that example?
@slolo - I'll have a look at your thread in just a moment.
Allan
HI Alan
Thank you very much. I have tested it against https://live.datatables.net/caxubosi/2/edit and all looks good. However, when I test on my example, I still see the problem that I mentioned on the 25 Feb: (last para)
I have updated the libraries on my example to match those in https://live.datatables.net/caxubosi/2/edit.
regards
Paul
Odd that it works in my example but not on your page! Can you use the non-minified files so I can get a better picture of what is happening when debugging please? Just remove the
.minpart from the file names for the nightly files.Allan
Hi Alan, I have removed the .mins. This is a copy of the data in the database so that you can see the date formatting.
Its taken me a number of iterations this afternoon to settle upon something that I'm reasonably happy with (and which actually works), but I've made a couple of changes to DataTables core and ColumnControl to address this. You example works correctly now - you might need to ctrl-f5 refresh your page to get the latest files.
The issue was that when the state was loaded the format hadn't yet been determined from the column, thus the saved data would fail to de-format, and no filter was applied.
Allan
Hi Alan, Thank you very much indeed for sorting this for me. I have also just checked the example and it works perfectly.
Awesome - glad to hear it is resolved at last. Again apologies for the delay in looking into it.
Allan