Query whereBetween with date

Hi, I'm playing with the whereBetween - query for 2 dates.

Given is a date 2018-10-04 01:02:03

So the whereBetween should ignore the hours and seconds, is that possible? $query->whereBetween('date', [$startDate, $endDate]);

with

$startDate = 2018-01-01; and $endDate = 2018-10-05;

works fine, if I set day in the $endDate to 04. it's not returning anything (for sure not).

Thank you.

Level 42

Ugly, but it works

DB::query()
        ->from('users')
        ->whereBetween(\DB::raw('DATE(date)'), [$startDate, $endDate])
        ->get();

Or on an Eloquent model:

User::whereBetween(DB::raw('DATE(date)'), [$startDate, $endDate])->get();

1 like

Best Answer

Level 122

if you set the end date to the end of the 4th or the start of the 5th then the hours and minutes become irrelevant

You dont show how you get the end date, but I would use this approach then you can use the standard eloquent whereBetween

2 likes

Level 42

@Snapey Good point, that would be

DB::query()
        ->from('users')
        ->whereBetween('date', [$startDate->startOfDay(), $endDate->endOfDay()])
        ->get();

User::whereBetween('date', [$startDate->startOfDay(), $endDate->endOfDay()])->get();

2 likes

Level 14

Thank you guys, that did the trick.

You dont show how you get the end date, but I would use this approach then you can use the standard eloquent whereBetween

Simple from the request as param, so I had to use Carbon::parse... and boom :)

Level 42

You should 'award' @Snapey the best answer, he proposed a better solution, I just provided the example code.

Level 14

Oh yeah, I was sure I clicked that, sorry!

Please or to participate in this conversation.