Laravel change date format in where clause to match Carbon::now()

php laravel date-formatting laravel-query-builder
Klav picture Klav · Dec 29, 2017 · Viewed 7.8k times · Source

I need to select entries based on dates happening in the future and the
entries contain the date format: 

12/30/17

I'm trying to format the date and compare to Carbon::now() timestamp, with no luck. 

$now = \Carbon\Carbon::now();

$bookings = DB::table('booking')
    ->select('booking.*')
    ->where('booking.uid', '=', Auth::id())
    ->where(DB::raw("(DATE_FORMAT(booking.date,'%Y-%m-%d 00:00:00'))"), ">=", $now)
    ->get();

Answer

aynber picture aynber · Dec 29, 2017

You'll need to use STR_TO_DATE to convert the string.

$bookings = DB::table('booking')
    ->select('booking.*')
    ->where('booking.uid', '=', Auth::id())
    ->where(DB::raw("(STR_TO_DATE(booking.date,'%m/%d/%y'))"), ">=", $now)
    ->get();

STR_TO_DATE will convert 12/30/17 to 2017-12-30

Related questions

How Do I Get the Query Builder to Output Its Raw SQL Query as a String?

Given the following code: DB::table('users')->get(); I want to get the raw SQL query string that the database query builder above will generate. In this example, it would be SELECT * FROM users. How do I do …

Read More
How to Create Multiple Where Clause Query Using Laravel Eloquent?

I'm using the Laravel Eloquent query builder and I have a query where I want a WHERE clause on multiple conditions. It works, but it's not elegant. Example: $results = User::where('this', '=', 1) ->where('that', '=', 1) …

Read More
Get the Last Inserted Id Using Laravel Eloquent

I'm currently using the below code to insert data in a table: <?php public function saveDetailsCompany() { $post = Input::All(); $data = new Company; $data->nombre = $post['name']; $data->direccion = $post['address']; $data->telefono = $post['phone']; $data->…

Read More