C# - Datetime search without using LIKE
C# - Datetime search without using LIKE
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
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%'
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.000This 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
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!
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.