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

Question

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

Chondrite

Why isn't your date in a standard format in your table in the first place? – Suggestibility

I wish it was. I have no control over that at this time. – Chondrite

@Chondrite what do you mean you have no control? You can write a simple query to change all the date rows to a single format. – Applaud

@AlexeyMezenin Because changing the date format in the table isn't the only thing that would need to be updated. – Chondrite

@Chondrite well, that's the only right way to go in this situation. Keeping rows in different formats is a nonsense. I doubt it's too hard to do related fixes (remove reinvented wheels and use Carbon instead). – Applaud

@AlexeyMezenin Yes, it's certainly the right way, but it's not a possibility. There is a solution to the current problem, no? I should be able to format the date in the query properly in order to compare times. – Chondrite

Answer 1

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

Suggestibility

Now okey i have change method and working fine @Suggestibility – Vandalize

Answer 2

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

Cinchona

Answer 3

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.

Applaud

I had originally tried that, but it doesn't pull the dates properly. >= pulls nothing and <= pulls all rows. – Chondrite