C# - Datetime search without using LIKE

guidols

Hi,

I have an editor table with some Datetime columns, defined in C# with:

.Field(new Field("Main.TestDate")
    .Validator(Validation.DateFormat(
        "dd.MM.yyyy",
        new ValidationOpts { Message = "Please enter a date in the format dd.MM.yyyy." }
        ))
    .GetFormatter(Format.DateSqlToFormat("dd.MM.yyyy"))
    .SetFormatter(Format.DateFormatToSql("dd.MM.yyyy"))
)

In JS I simply have:

{
    label: "Test Date:",
    name: "Main.TestDate",
    type: "datetime"
},

I have also individual column server-side searching, which is working fine except for the date fields.

The problem is when I search for a date (using any string, like 09.08.2022 or 2002-08-09): the Editor generates a query using a LIKE statement, which in MS SQL Server is not working.

Let's forgot the format, but for example, if I search for 2002-08-09, the resulting query (when using the editor debug) is:

SELECT
...
WHERE [Main].[TestDate] LIKE '2002-08-09'

which is not working in MS SQL Server.

The query should be:

SELECT
...
WHERE [Main].[TestDate] = '2002-08-09'

How can I fix?

Thanks!

rf1234

I am surprised. Why is "LIKE" not working with SQL Server? I remember using it when I still used SQL Server.

This here states the same: https://www.sqlservertutorial.net/sql-server-basics/sql-server-like/

I am not the author of this but I don't think there is a fix in case you really can't use LIKE. Then you would have to drop serverSide, I think.

This LIKE looks wrong:
LIKE '2002-08-09'
It should be
LIKE '%2002-08-09%'

guidols

Hi,

sorry but my first message had a typo in the query.

The issue is that LIKE is not working with datetimes.

i.e.

If I have a row with the following date: 2022-07-10 00:00:00.000

This returns the row: SELECT * FROM ... WHERE Date = '2022-07-10'

This doesn't: SELECT * FROM ... WHERE Date LIKE '%2022-07-10%'

I need a way to tell the Editor to use the = and not the LIKE operator.

Thanks

rf1234

I checked this and there wasn't anything applicable to your case. I guess you will need to drop serverSide or live with it.
https://datatables.net/manual/server-side#Sent-parameters

You might be able to change the SQL server settings or report a bug to Microsoft. It works fine with MariaDB!