How to use WhereBetween date with %LIKE% in sql query

Question

In my database creadted_at data 2017-11-07 18:58:16,2017-11-07 19:58:16. I try to use WhereBetween for searching data in date 2017-11-07 Am not sure How can I put Like % % into my query

 'like', '%'.2017-11-07.'%'

 ->WhereBetween('created_at', ['2017-11-07', '2017-12-07'])

Here is my full controller

  $b = DB::table("v_dealer_sell_time")
             ->select([
                    'product_name',
                    DB::raw('COALESCE(SUM(total_price), 0) AS total_price'),
                    DB::raw('COALESCE(SUM(total_product), 0) AS total_product')
                    ])
            ->WhereBetween('created_at', ['2017-11-07', '2017-11-07'])
            ->groupBy('product_name')
            ->get();

You want data that are created in the same day as 2017-11-07 — Maraboc, Oct 18 '17 at 15:24

score 1 · Answer 1 · answered Oct 18 '17 at 15:26

Use whereDate():

$date = Carbon::parse($date)->toDateString();
....
->whereDate('created_at', $date)

Or whereBetween():

$date = Carbon::parse($date);
$from = $date->copy()->startOfDay();
$to = $date->copy()->endOfDay();
....
->whereBetween('created_at', [$from, $to])

Maraboc · Accepted Answer · 2017-10-18T15:38:12.007

If you want the created ones in the same given date you can use whereDate like this But since 5.3 (Documentation):

$b = DB::table("v_dealer_sell_time")
             ->select([
                    'product_name',
                    DB::raw('COALESCE(SUM(total_price), 0) AS total_price'),
                    DB::raw('COALESCE(SUM(total_product), 0) AS total_product')
                    ])
            ->WhereDate('created_at', '=', $date)
            ->groupBy('product_name')
            ->get();

If you want it from between two dates use whereBetween like this :

$b = DB::table("v_dealer_sell_time")
             ->select([
                    'product_name',
                    DB::raw('COALESCE(SUM(total_price), 0) AS total_price'),
                    DB::raw('COALESCE(SUM(total_product), 0) AS total_product')
                    ])
            ->WhereBetween('created_at', [Carbon::parse('2017-11-07')->startOfDay(), Carbon::parse('2017-12-07')->endOfDay()])
            ->groupBy('product_name')
            ->get();

PS : Do not forget to add use Carbon\Carbon; at the top of your Controller.

Opps i updated it's between 2017-11-07 and 2017-12-07 – user8663822 Oct 18 '17 at 15:29 — user8663822, Oct 18 '17 at 15:29

Abdulla Nilam · Answer 3 · 2017-10-18T15:35:50.570

1

If this Date search related to a Search Function, then you have to do something like this

Code

Change this to

->WhereBetween('created_at', ['2017-11-07', '2017-11-07'])

this

->WhereBetween('created_at', ['2017-11-07 00:00:00', '2017-11-07 23:59:59'])

WHY ??

2017-11-07 00:00:00 - Start of the day
2017-11-07 23:59:59 - end of the day

if you use tosql() and check your query it has something like this

.`created_at` between ? and ? "

edited Oct 18 '17 at 15:35

answered Oct 18 '17 at 15:33

Abdulla Nilam

36,589
17
64
85

may I ask you to take a look at a Laravel search related question here: https://stackoverflow.com/questions/76485513/laravel-search-with-multiple-keywords-against-multiple-columns-with-the-search? – Istiaque Ahmed Jun 15 '23 at 21:39

How to use WhereBetween date with %LIKE% in sql query

3 Answers3