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álokTérinformálok Posts: 5Questions: 2Answers: 0


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.

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!)
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

How can the time be ignored somehow?

I don't want to split the datetime column into date + time columns
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 )
(under Custom formatters)

->setFormatter( function ( $val, $data ) {
echo "############";
return $val
? 'Done'
: 'To Do';
return '$val';
} )

best regards

This question has an accepted answers - jump to answer


  • sandysandy Posts: 913Questions: 0Answers: 236
    Answer ✓

    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 $val1 as required before making the where condition.


  • TérinformálokTérinformálok Posts: 5Questions: 2Answers: 0

    Thanks for the reply, I think this solution might work.


Sign In or Register to comment.