C# - Datetime search without using LIKE

C# - Datetime search without using LIKE

guidolsguidols Posts: 29Questions: 11Answers: 1
edited August 9 in Editor

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!

Answers

  • rf1234rf1234 Posts: 2,444Questions: 78Answers: 353
    edited August 9

    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%'

  • guidolsguidols Posts: 29Questions: 11Answers: 1

    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

  • rf1234rf1234 Posts: 2,444Questions: 78Answers: 353

    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!

Sign In or Register to comment.