My Date format is 26-June-2020 and i run sql query
SELECT * FROM `customers` WHERE date BETWEEN '26-June-2020' AND '30-June-2020'
but its not working good its getting record from 26-june-2020 to 3-July-2020
and my laravel code is this:
$customers = Customer::whereBetween('date', array($datefrom, $dateto))->get();
You first need to convert the dates to Carbon objects
$from = Carbon::parse($dateFrom)
->startOfDay()
->toDateTimeString();
$to = Carbon::parse($dateTo)
->endOfDay()
->toDateTimeString();
$customers = Customer::whereBetween('date', [$from, $to])->get();
I think the problem is date format of $datefrom $dateto, I found a issue like your question How to query between two dates using Laravel and Eloquent?. Hoping it help you.
If your data is in the format (e.g.) 01-June-2020 that means that your data is not correctly formatted as dates. This means you need to convert them to dates before getting a correct BETWEEN result. Not doing so will yield you a BETWEEN result comparing dates as strings, which will mean 01-June-2020 will come before 02-January-2020.
$customers = Customer::whereBetween(
DB::raw("STR_TO_DATE(date, '%d-%M-%Y')"),
array($datefrom, $dateto) // These should also be correctly formatted as YYYY-MM-DD or as carbon objects
)->get();
However the major drawback of this approach is that you cannot use any indexes making any queries much slower than they could be if you permanently covert the given column to date objects and add an index.
This solution utilises STR_TO_DATE
'26-June-2020' AND '30-June-2020'