Date search not working when using Server Side

daveng

I have a table containing about 200 000 rows, so I need to use Server Side.
I have a column with a date (stored as datetime in a SQL Server) but displayed as d/M/yyyy in the table.
If I do a search for a date, it won't work (it works if I turn off server side, using a smaller dataset). I'm guessing that not using server side, the search will try to match the string inside the code and using server side, it will so a sql query where the date typed in the search field will not match the datetime format.
I've tried typing the date in datetime format, it doesn't work either.

We do have some conversion taking place, so when the we add/update a row using d/M/yyyy format, it will convert it and store it as a regular datetime in the SQL Server.

Here's what we have in the C# code regarding this particular column:

.Field(new Field($"{certificationsDBSetName}.InvoicingDate")
                            .SetFormatter(Extensions.ParseStringToDateTime("d/M/yyyy", true))
                            .GetFormatter(Extensions.ParseDateTimeToString("d/M/yyyy"))
                    )

With the following functions:

public static Func<object, Dictionary<string, object>, object> ParseStringToDateTime(string format, bool onlyDate = false)
        {
            return (val, data) =>
            {
                if (string.IsNullOrWhiteSpace(val.ToString()))
                    return null;

                DateTime dateTime;
                DateTime.TryParseExact(val.ToString(), format, CultureInfo.InvariantCulture, DateTimeStyles.None, out dateTime);
                //DateTime.ParseExact(val.ToString(), format, CultureInfo.InvariantCulture);

                if (onlyDate)
                    return dateTime.Date;

                return dateTime;
            };
        }

        public static Func<object, Dictionary<string, object>, object> ParseDateTimeToString(string format)
        {
            return (val, data) => val != null ? ((DateTime)val).ToString(format, CultureInfo.InvariantCulture) : val;
        }

Is there a way to make the search work (at this point we only need exact date search, not range)

Thanks for any help!

allan

Hi,

With server-side processing the search is performed by the SQL engine, so what you are searching for needs to be in the same format as the data you are searching against. Most likely the database will be using ISO8601 date format, so if you use:

.Where($"{certificationsDBSetName}.InvoicingDate", "2019-08-06")

I would expect it to correctly do a date search. Could you test if that is the case for your db? If not, how is the date being stored?

Assuming that does work, then the key will be to convert from the date format submitted to the server into ISO8601 for the Where condition. How are you currently applying the condition?

Allan

daveng

Hi Allan,

Thanks! You are right, it works this way. But I'm not applying the condition at the moment. I'm using search per columns as shown is this example: https://datatables.net/examples/api/multi_filter.html

So I don't actually have a where condition applied. Where should I put it?

allan

The .NET documentation for Editor conditions should help there. Put it into the Editor instance construction on the server-side.

Allan

daveng

Thanks Allan...

I managed to get a bit further, but I'm still not finding a way to get the value of the input field for the search. Maybe you can help me out?

Here what I have in the controller:

Editor.Where( q => {
   q.Where($"{certificationsDBSetName}.IssueDate", "(SELECT convert(varchar, IssueDate, 23) FROM dbo.Certifications where CONVERT(VARCHAR(25), IssueDate, 103) LIKE '%:issue-date%')", "IN", false);
   q.Bind(":issue-date", Request.Form["issue-date"]);
   })

The SQL request works fine, if I replace :issue-date in the select by a date like "04/2017", it will output the right records.

So for example using this code works fine:

Editor.Where( q => {
   q.Where($"{certificationsDBSetName}.IssueDate", "(SELECT convert(varchar, IssueDate, 23) FROM dbo.Certifications where CONVERT(VARCHAR(25), IssueDate, 103) LIKE '%04/2017%')", "IN", false);
   })

It is when I introduce the binding and try to get the value from the input field that it doesn't work.

The search on the webpage is using this (this came from one of your example):

certificationsDataTable.columns().every(function () {
   var that = this;
   $('input', this.footer()).on('keyup change', function () {
      if (that.search() !== this.value) {
         that
            .search(this.value)
            .draw();
      }
   });

Also I create the search fields with this:

if (search_cols.indexOf(col) !== -1) {
   $(this).html('<input class="form-control" type="text" name ="' + title.replace(/\s+/g, '-').toLowerCase() + '" placeholder="Search ' + title + '" />');
   }

So for instance the field I'm searching from has this code in the page:

<input class="form-control" type="text" name="issue-date" placeholder="Search Issue Date">

Sorry if I sound dumb, and any help will be greatly appreciated!

allan

Does it work if you have the following?

Editor.Where( q => {
   q.Where($"{certificationsDBSetName}.IssueDate", "(SELECT convert(varchar, IssueDate, 23) FROM dbo.Certifications where CONVERT(VARCHAR(25), IssueDate, 103) LIKE '%:issue-date%')", "IN", false);
   q.Bind(":issue-date", "04/2017");
   })

Trying to narrow the issue down to the binding or the data being sent from the client-side.

Allan

daveng

Good idea!
It doesn't work actually... it returns no rows at all.

I don't know if it helps but this will return results:

Editor.Where( q => {
   q.Where($"{certificationsDBSetName}.IssueDate", "(SELECT convert(varchar, IssueDate, 23) FROM dbo.Certifications where CONVERT(VARCHAR(25), IssueDate, 103) LIKE '%04/2017%')", "IN", false);
   })

This will return zero result:

Editor.Where( q => {
   q.Where($"{certificationsDBSetName}.IssueDate", "(SELECT convert(varchar, IssueDate, 23) FROM dbo.Certifications where CONVERT(VARCHAR(25), IssueDate, 103) LIKE '%04/2017%')", "IN", false);
   q.Bind(":issue-date", Request.Form["issue-date"]);
   })

So adding the Bind make makes the request return nothing, even though I'm not using the bind in the query.

allan

That's weird! I'll try that locally shortly and let you know if that happens here too.

Allan

daveng

Hi Allan,
Were you able to do some tests? I tried different options (although there weren't so much I can think of) and got no luck on my side...

allan

Sorry. I've just looked at it - the problem is that the binding character for the .NET libraries (by default) is @ not :! I'd completely forgotten about that...

Allan