Level 1
why can't you use something like this SELECT * FROM room_reservations WHERE checkinDate >= '2022-03-19' and checkoutDate < '2022-03-27';
keep in mind, I wrote this query to run in the db and to test, you should turn this into orm
1 like
Mar 13, 2022
3
Level 1
Get data between date from 2 field in Laravel
I have data like this:
id | employee_id | absent_type | do_date_start | do_date_end
1 | EMPLOYEE-1 | Absent | 2022-02-14 00:00:00 | 2022-02-18 00:00:00
What I want to do is to get data between field do_date_start and do_date_end.
Case example using data above:
Start Date | End date | Expected Result
2022-02-14 | 2022-02-18 | Data Found
2022-02-13 | 2022-02-19 | Data Found
2022-02-15 | 2022-02-17 | Data Found
2022-02-13 | 2022-02-17 | Data Found
2022-02-19 | 2022-02-20 | Data Not Found
2022-02-12 | 2022-02-13 | Data Not Found
So as long as the date between do_date_start and do_date_end is between filter start date and end date it still getting the data, if not it won't getting the data.
I've tried like this:
DB::table('attendance_absent as absent')
->where(function($query) use ($start_date, $end_date){
$query->where('absent.do_date_start', '<=', $start_date)
->where('absent.do_date_end', '>=', $end_date)
->orWhere(function ($q) use($start_date, $end_date) {
$q->where('absent.do_date_start', '>=', $start_date)
->where('absent.do_date_end', '<=', $end_date)
->orWhere(function ($k) use ($start_date, $end_date) {
$k->where('absent.do_date_start', '>=', $start_date)
->where('absent.do_date_end', '>=', $end_date)
->orWhere(function ($l) use ($start_date, $end_date) {
$l->where('absent.do_date_start', '<=', $start_date)
->where('absent.do_date_end', '<=', $end_date);
});
});
});
})
->get();
When I tested my code using case example above, the result is like this:
Start Date | End date | Expected Result | Correct?
2022-02-14 | 2022-02-18 | Data Found | Yes
2022-02-13 | 2022-02-19 | Data Found | Yes
2022-02-15 | 2022-02-17 | Data Found | Yes
2022-02-13 | 2022-02-17 | Data Found | Yes
2022-02-19 | 2022-02-20 | Data Not Found | No
2022-02-12 | 2022-02-13 | Data Not Found | No
Somehow I still getting the data even though the data is outside date range, I don't know what's wrong with my code. Any help would be very helpful for me, thanks
Level 1
@krisssachin Well I've tried that before
DB::table('attendance_absent as absent')
->where('absent.do_date_start', '<=', $filter_date)
->where('absent.do_date_end', '>=', $end_date)
->get();
The result is incorrect in the second and forth test case
Start Date | End date | Expected Result | Correct?
2022-02-14 | 2022-02-18 | Data Found | Yes
2022-02-13 | 2022-02-19 | Data Found | No
2022-02-15 | 2022-02-17 | Data Found | Yes
2022-02-13 | 2022-02-17 | Data Found | No
2022-02-19 | 2022-02-20 | Data Not Found | Yes
2022-02-12 | 2022-02-13 | Data Not Found | Yes
Level 1
@catto you did it for two columns as do_date_start and do_date_end, what I suggest is to keep a seperate column called dates and add the dates for each day
->where('the_date','<=',$filter_date) ->where('the_date','>=',$end_date)->get();
something like this
Please or to participate in this conversation.