Like mysql Date

[milo]

Is there a way to do a like statement with query builder? I am trying to achieve

WHERE data.arrival LIKE ‘%2019-11-29%’ AND data.arcitycode = ‘bwi’.

The date and city code being url variables. The date I am searching is a datetime field, but I just need the date not the time.

thanks

[Teodor]

Please check:

No LIKE condition for mysql?

I realy like using LIKE as a condition and this allow me to use % as a default value if the parameter is empty but it’s missing from condition list in db query… I can do it in with a custom query and works fine. (i love the test query feature there anyway) eg: SELECT * FROM Tasks WHERE Tasks.t_status LIKE 1 AND Tasks.fk_member_id = 2 1 = {{$_GET.t_status.default("%")}} Just curious why it’s missing for db query. thanks /J

[milo]

What do the emojis represent?

[Teodor]

Not sure what you mean, but click on the link i posted and check the discussion please.

[milo]

contains does not exist in the query builder.

[Teodor]

Contains is available for strings only.

[milo]

So there is no way to do a contains with a date? sorry a little lost. I can put the LIKE in a custom query, but not sure how to get the url variable in the statement with the % :P1 % for example

[Teodor]

Well, no that’s not possible - but i am not sure i understand what are you trying to achieve?

[milo]

I simply want to query a date, but just the date part of the datetime field.

[Teodor]

How do you filter it - where does the filter value(s) come from?

[milo]

URL variable. example ?arrivaldate=‘2019-11-29’.

[Teodor]

Do you store hours or it is just the default 00:00:00 as you are using DATETIME database fields?

[milo]

there are hours and minutes in the date time field. This is a list of flights. all have arrival times, but I want to just get everything for the day. Ignoring the time.

[Teodor]

Then you can use the between condition and then format the date, in the format you need using the data formatter:

{{$GET_variable.formatDate("yyyy-MM-dd 00:00:00")}}

and

{{$GET_variable.formatDate("yyyy-MM-dd 23:59:59")}}

[milo]

Perfect! thank you