Can I search in SearchBuider by date only in the datetime column?
Can I search in SearchBuider by date only in the datetime column?
Térinformálok
Posts: 5Questions: 2Answers: 0
hello,
I have a datetime column in my database. (mssql server, and the column name is: fa.FA.FELVETEL_IDEJE)
I use searchPane , but it is not interesting now.
server side:
Field::inst( 'fa.FA.FELVETEL_IDEJE' )
->searchPaneOptions( SearchPaneOptions::inst())
->validator( Validate::dateFormat( 'Y-m-d' ) )
->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
->setFormatter( Format::dateFormatToSql('Y-m-d' ) )
When I try to search using sqlbuilder, I run into the following problem.
When I give it a date, but only the date, the sql filter does not find a result.
So:
fa.FA.FELVETEL_IDEJE = '2022-01-05' then no result, this is all ok (?) , because the records in the datetime column in the database contain the time too!)
fa.FA.FELVETEL_IDEJE
row1: 2021-01-05 11:11:02
row2: 2021-01-05 11:12:03
rown: 2021-01-05 11:13:01
It is therefore understandable (?) that
fa.FA.FELVETEL_IDEJE = '2022-01-05' = no result (as far as I know, if no time is given, sql adds a default time, which in this case is 00:00:00)
but, if I specify it like this:
fa.FA.FELVETEL_IDEJE = '2022-01-05 11:11:02' = 1 result
Question:
How can the time be ignored somehow?
I don't want to split the datetime column into date + time columns
Or,
I tried to use this function on the server side, by reference, but it doesn't work in the function either! (that's why the echo is included, and I'm watching the debug mode of course )
https://editor.datatables.net/manual/php/formatters
(under Custom formatters)
->setFormatter( function ( $val, $data ) {
echo "############";
return $val
? 'Done'
: 'To Do';
return '$val';
} )
best regards
Máté
This question has an accepted answers - jump to answer
Answers
Hi @Térinformálok ,
I'm afraid this isn't something that is supported out of the box for SearchBuilder. That being said, you may be able to edit the serverside scripts to fit your needs. The code for the SearchBuilder
=condition can be found here. You could use$crit['origData']to get the field name and then adjust$val1as required before making the where condition.Thanks,
Sandy
Thanks for the reply, I think this solution might work.
Regards,
Máté