I want to get all the rows from a table through an expression:
table.date <= 2014-07-10
But if the column contains a datetime let's say:
But if I do:
where('date', '<=', $date)
it won't get the row.
I guess this is because $date = 2014-07-10 which makes MySQL assume that it is 2014-07-10 00:00:00.
In regular MySQL I would just do
where DATE(date) <= $date
What would be the equivalent using Laravel's Eloquent?
This question is tagged with
~ Asked on 2014-08-05 13:23:44
They do the SQL
DATE() work for you, and manage the differences of SQLite.
Your result can be achieved as so:
->whereDate('date', '<=', '2014-07-10')
Update: Though the above method is convenient, as noted by Arth it is inefficient on large datasets, because the
DATE() SQL function has to be applied on each record, thus discarding the possible index.
Here are some ways to make the comparison (but please read notes below):
->where('date', '<=', '2014-07-10 23:59:59') ->where('date', '<', '2014-07-11') // '2014-07-11' $dayAfter = (new DateTime('2014-07-10'))->modify('+1 day')->format('Y-m-d'); ->where('date', '<', $dayAfter)
~ Answered on 2015-09-28 22:10:36
Have you considered using:
where('date', '<', '2014-08-11')
You should avoid using the
DATE() function on indexed columns in MySQL, as this prevents the engine from using the index.
As there seems to be some disagreement about the importance of
DATE() and indexes, I have created a fiddle that demonstrates the difference, see
~ Answered on 2014-08-05 13:27:54