Search Date Format

dpanscik

Today I noticed "Search" requires a date to be in a format different than the date in datatable.

In datatable im using the date format of ddd, DD MMM YYYY" but search wants dates in the format of yyyy-mm-dd

How can I setup search to use date format of 'DD MMM YYYY' ?

kthorngren

Are you using a date renderer like this example to display the dates? Searching by the rendered data seems to work.

If you still need help then please provide a simple test case showing an example of what you have and how you are displaying the dates.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

dpanscik

Hi Kevin,

I am all ajax, so searching is done on the server.

kthorngren

In that case the date format will need to be converted to what is stored in the database. The mechanics of that depend on how you are doing the search. Are you using a datetime input like this?

Kevin

dpanscik

Here is a better visual of what is going on.

I have a stock search box & three date columns.

If i search by "Tue" I get no results

if i search by "14 Mar" I get no results

if i search by "2023-03-14" I get results

I want to make sure I get results when I search for "14 Mar" and if it is not to much trouble also get results when I search for "Tue"

I initialize search by dom (i have no idea which letter is "search" but I know its in there somewhere) dom: "lBfrtip",

Everything is ajax

            "ajax": {
                "url": "/api/APData",
                "type": "POST",
                "datatype": "json",
                "data": function (d) {
                    return $.extend({}, d, {
                        "form_id": "APgrid",
                        "displaypaid": $('#displayPaid:checked').val(),
                        "displaynotpaid": $('#displayNotPaid:checked').val(),
                     });
                }

            },

so ideally I catch the date formatting and change it before the AJAX post, or I catch it in the server controller and change the formatting there. Either solution must not break other non date searches.

The controller is "stock" editor controller

/*
 * Controller for DB table Flex_OnCall_3
 * Created by http://editor.datatables.net/generator
 */
using System;
using System.Collections.Generic;
using System.Net.Http.Formatting;
using System.Web;
using System.Web.Http;
using DataTables;
using EditorGenerator.Models;

namespace EditorGenerator.Controllers
{
    public class APDataController : ApiController
    {
        [Route("/api/APData")]
        [HttpGet]
        [HttpPost]
        public IHttpActionResult Flex_OnCall_3()
        {
            var request = HttpContext.Current.Request;
            var settings = Properties.Settings.Default;

            using (var db = new Database(settings.DbType, settings.DbConnection))
            {


                var response = new Editor(db, "AP_Data", "TableID")
                    .Model<AP_DataModel>()
                    .Field(new Field("today")

                    .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_2822))
                    .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822)))
                    .Field(new Field("InvoiceDate")
                    .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_2822))
                    .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822)))
                    .Field(new Field("DueDate")
                    .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_2822))
                    .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822)))
                    .Field(new Field("AddedIntoQbDate")
                    .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_2822))
                    .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822)))
                    .Process(request)
                    .Data();

                return Json(response);
            }
        }
    }
}

dates in the view are rendered like this

         var fnRenderDate = function (data, type, row) {

            return moment(new Date(data)).locale('en').format('ddd, DD MMM YYYY');
        };

           "columns": [


                { "data": "EmailReceivedDate", "title": "EmailReceivedDate", "name": "EmailReceivedDate", "autoWidth": true, render: fnRenderDate },
                { "data": "InvoiceDate", "title": "InvoiceDate", "name": "InvoiceDate", "autoWidth": true, render: fnRenderDate },
                { "data": "DueDate", "title": "DueDate", "name": "DueDate", "autoWidth": true, render: fnRenderDate },
                { "data": "AddedIntoQbDate", "title": "AddedIntoQbDate", "name": "AddedIntoQbDate", "autoWidth": true, render: fnRenderDate },

            ],

kthorngren

I initialize search by dom (i have no idea which letter is "search" but I know its in there somewhere) dom: "lBfrtip"

See the dom docs for what the letters mean. The f is the default global search input.

so ideally I catch the date formatting and change it before the AJAX post, or I catch it in the server controller and change the formatting there. Either solution must not break other non date searches.

Yes, that is correct. This could be done but would take a good bit of coding to make sure the text being typed is a date and not just some other text. If it were me I would use a Datetime picker for date searching. This example uses the Datetime extension with server side processing.
https://live.datatables.net/qoliyehi/7/edit

The dates in the database look like 2008-11-28. The display and date picker renders as ddd, DD MMM YYYY. You can see in the browser's console the date format is converted to the server format. Also looking at the browser's network inspector you will see the same conversion, ie, 2008-11-28.

There seems to be a bug with the Datetime extension that @allan will look at. For example selecting the date Fri, 28 Nov 2008 results in the 2008-11-27. To test make sure to select one day in advance. Try Fri, 29 Nov 2008 to have the result of Airi Satou to be shown.

Kevin

allan

Hi both,

Apologies for the error in DateTime. There was a timezone bug in 1.3. It has since been addressed in 1.4.0 and should work correctly now: https://live.datatables.net/qoliyehi/8/edit .

(I thought I'd made the live site versions auto updating - apparently that isn't working as its using some old versions... on that case).

Allan

kthorngren

I updated the test case with extra console output and with 1.4.0 its still picking the previous day.
https://live.datatables.net/pukiboja/1/edit

If I select March 15 this is the output:

Selected date: Tue Mar 14 2023 20:00:00 GMT-0400 (Eastern Daylight Time)
Searching for date: 2023-03-14

Kevin

allan

Ah - sorry. I'll test it with a changed timezone after lunch (off to see Sandy ). I suspect the issue is:

return moment(new Date(data))

being local time. It should perhaps be:

return moment.utc(new Date(data))

but I'll check shortly

Allan

allan

Yup - a timezone issue when using Moment for the formatting. Fortunately DateTime now has a valFormat() method which can be used to get the value in a given format and it will "nullify" the timezone stuff for you in this case.

var val = myDate.valFormat('YYYY-MM-DD');

is how to get the value: https://live.datatables.net/pukiboja/2/edit .

Allan

dpanscik

I do believe the owners of this forum have hijacked my thread! Now.. that's funny!

Here is my solution to reformat a date typed in "Search" as DD MMM YYYY to yyyy-mm-dd

this intercept happens on the server inside of the MVC controller.

the idea is a 4 step process;
1 - check if the Search Key value pair has a month name
2 - if so remove the Search Key value pair
3 - reformat the date honoring wildcards
4 - put the key value pair back into the string.

            if (request.GetValues("search[value]") != null) { 
                searchString = request.Get("search[value]").ToString();
                string newSearchString = "";
                string monthValue = "%%%";
                string dayValue = "%%";
                string yearValue = "%%%%";
                string[] month_array = new string[] { "january", "jan","february","feb","march","mar","april","apr","may","june","jun","july","jul","august","aug","september","sept","sep","october","oct","november","nov","december","dec" };
                foreach (string monthCheck in month_array)
                {
                    if (searchString.IndexOf(monthCheck, 0, StringComparison.OrdinalIgnoreCase) != -1)
                    {
                        monthValue = monthCheck;
                        // remove month word and repalce with -
                        searchString = System.Text.RegularExpressions.Regex.Replace(searchString, monthCheck, "-");
                        // remove all spaces
                        searchString = System.Text.RegularExpressions.Regex.Replace(searchString, " ", "");

                        if (string.Concat(searchString.TakeWhile((c) => c != '-')) != "")
                        {
                            dayValue = string.Concat(searchString.TakeWhile((c) => c != '-'));
                            //make a 1 digit day, a %1 digit day
                            if (dayValue.Length < 2)
                            {
                                dayValue = "0" + dayValue;
                            }       


                        }
                        if (searchString.Split('-').Last() != "")
                        {
                            yearValue = searchString.Split('-').Last();
                            // check for 2 digit year and if so make it a 4 digit year
                            if (yearValue.Length < 2) { yearValue = "%" + yearValue; }
                            if (yearValue.Length < 3) { yearValue = "%" + yearValue; }
                            if (yearValue.Length < 4) { yearValue = "%" + yearValue; }

                        }
                        
                        monthValue = Convert.ToDateTime(monthValue + " 01, 1900").Month.ToString();
                        if (monthValue.Length < 2)
                        {
                            monthValue = "0" + monthValue;
                        }
                        newSearchString = yearValue + "-" + monthValue + "-" + dayValue;
                        request.Remove("search[value]");
                        request.Add("search[value]", newSearchString);

                        break;

                    }
                }

            }