get data if a date is between start_date and end_date

I have following code

$date = Carbon::today()->format('d-m-Y');

Want to retrieve data if user_id matches and the $date is between start_date and end_date

Leave::where('user_id', $user->id)
            ->where(function ($query) use ($date) {
                $query->where('start_date', '<=', $date)
                    ->where('end_date', '>=', $date);
            })->get();

The above query does not return any data.

Note: the value of $date => '09-02-2022', start_date => '2022-02-08 00:00:00' and end_date => '2022-02-09 10:30:00'

NemoPS

4 years ago

Level 1

Hmm why are you using the closure? You should be able to chain where clauses.

edit: are they in that format? I believe the format should match what's in the database

vishytk

4 years ago

Level 1

I have tried the following, but getting same result

Leave::where('user_id', $user->id)
                ->where('start_date', '<=', $date)
                ->where('end_date', '>=', $date)
            ->get();

Sofia

4 years ago

Best Answer

Level 6

Use 'Y-m-d' to format your date. If it's still not working, inspect the query that's being generated and post here.

vishytk

4 years ago

Level 1

@Sofia changing the format $date format to 'Y-m-d' worked.

Thanks

Please or to participate in this conversation.