CODEWITHSUNDEEP
phplaraveldate-formattinglaravel-query-builder
Klav
Klav

Reputation: 405

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

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();

Upvotes: 5

Views: 20509

Answers (3)

Nurullah
Nurullah

Reputation: 102

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

Upvotes: 1

aynber
aynber

Reputation: 23011

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

Upvotes: 10

Alexey Mezenin
Alexey Mezenin

Reputation: 163758

I don't think you really need to check date format. Also, you have some redundand stuff in the query. Just do this:

Booking::where('uid', auth()->id())->where('date', '>=', now())->get();

And if the date format is really different in some of the rows in the same column, you really need to fix this and instead of making some dirty fix for that.

Upvotes: 0

Related Questions