convert created_at column in laravel to y-m-d format in whereBetween query

this is my controller

public function index(){
    $products = Order::whereHas('user', function ($query) {
    if(request()->has('d') && request()->get('d')){
        $arr = [
           'start' => Carbon::parse(substr(request()->get('d'), 4, 11))->format('Y-m-d'),
           'end' =>  Carbon::parse(substr(request()->get('d'), 64, -44))->format('Y-m-d')
                ];
            $builder = $query->whereBetween('created_at', [$arr['start'],$arr['end']]);
           }
        });
   return OrdersResource::collection($orders->latest()->paginate(5));
    }

my array $arr looks like this
array:2 [
  "start" => "2018-08-07"
  "end" => "2018-08-09"
]

How can i change the created_at (which is a datetime) column in laravel to only Year month day format

I need to convert the created_at to that format in order to perform the whereBetween query of laravel because it didn’t give me the exact result

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Instead of change the format of the created_at column, I suggest that you should update the start and end to beginning of the first date and the ending of the last date. The Carbon objects should be passed to the whereBetween method instead of their string representation.

$arr = [
   'start' => Carbon::parse(substr(request()->get('d'), 4, 11))->startOfDay(),
   'end' =>  Carbon::parse(substr(request()->get('d'), 64, -44))->endOfDay()
];

// Both `start` and `end` are Carbon objects
$builder = $query->whereBetween('created_at', [$arr['start'],$arr['end']]);

Method 2

You can used mysql DATE() function

$builder = $query->whereBetween(DB::raw('DATE(created_at)'), [$arr['start'],$arr['end']]);

if you want to change the date format, DATE_FORMAT(created_at, '%Y-%m-%d')

Dont forget to import DB

use DB;


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x