C# - Datetime search without using LIKE

C# - Datetime search without using LIKE

guidolsguidols Posts: 36Questions: 14Answers: 1
edited August 2022 in Editor


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

.Field(new Field("Main.TestDate")
        new ValidationOpts { Message = "Please enter a date in the format 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:

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

which is not working in MS SQL Server.

The query should be:

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

How can I fix?



  • rf1234rf1234 Posts: 2,624Questions: 80Answers: 379
    edited August 2022

    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: 36Questions: 14Answers: 1


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

    The issue is that LIKE is not working with datetimes.


    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.


  • rf1234rf1234 Posts: 2,624Questions: 80Answers: 379

    I checked this and there wasn't anything applicable to your case. I guess you will need to drop serverSide or live with it.

    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.