C# - Datetime search without using LIKE

C# - Datetime search without using LIKE

guidolsguidols Posts: 38Questions: 14Answers: 1
edited August 2022 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,810Questions: 85Answers: 406
    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: 38Questions: 14Answers: 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,810Questions: 85Answers: 406

    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!

  • AllNetAllNet Posts: 1Questions: 0Answers: 0

    Please, try divided request process and read params for date search and add custom query before process all data for request.

    First part:
    Read dates values
    minValueDate = request.Form.GetValues("minDate").FirstOrDefault();
    maxValueDate = request.Form.GetValues("maxDate").FirstOrDefault();

    Second part:

    var editor = new Editor(db, "my_table")
    .Debug(true)
    .Model<MyTable>()
    .Field(new Field("my_fields");

    Third part:

    if (!string.IsNullOrEmpty(minValueDate)){
    var response= editor
    .Where(q => q.Where("convert(varchar, [created], 23)", $"(SELECT convert(varchar, [created], 23) FROM [my_table] where CONVERT(VARCHAR(25), [created], 23) LIKE '%{minValueDate}%')", "IN",false))
    .Process(request)
    .Data();
    var respuesta = Json(response);
    return Json(response);
    }else{
    var response= editor
    .Process(request)
    .Data();
    return Json(response);
    }

    Frontend Ajax Request:

    ajax: {
    url: urlApi,
    type: 'POST',
    data: function (d) {
    d.minDate = $('#min').val();
    d.maxDate = $('#max').val() !== '' ? $('#max').val() : moment().toDate().format('yyyy-MM-dd');
    }
    }

    Regards.

Sign In or Register to comment.