Laravel where dates by specific format

Question

In DB Col Name('publish') varchar format d/m/Y for Example 30/1/2020 when try to get dates less than 30/1/2020 get any date less than days

for example

->where('publish','<','30/01/2020')

29/2/2020 less than 30/1/2020 so i get any date under 30 by day not month or year

make your string date as carbon object as Carbon::parse('30/01/2020') then you can compare it as date easily — Moshiur
– Moshiur, Commented Feb 13, 2020 at 9:03

Moshiur · Accepted Answer · 2020-02-13 09:30:30Z

1

on your Eloquent model define an accessor to retrive publish as carbon object

public function getPublishAttribute($date)
{
    return Carbon::parse($date)->format('d/m/Y');
}

now on your controller compare both date as below

->where('publish','<',Carbon::parse('30/01/2020'))

hope it helps!

answered Feb 13, 2020 at 9:30

Moshiur

6937 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Egyptionl Over a year ago

you can't parse Carbon::parse('30/01/2020') normal format for parse is m/d/Y

Moshiur Over a year ago

Carbon::parse('30/01/2020') will convert the date as 'Y/m/d' format, but it will work fine for condition as both in date instance

Sehdev · Accepted Answer · 2020-02-13 10:47:51Z

0

You can use DB::raw and str_to_date for the same:

 ->where(DB::raw('str_to_date(publish, "%d/%m/%Y")'),'<','30/01/2020' )

str_to_date will convert your varchar field to datetime field.

MySQL -> Date and Time Functions-> STR_TO_DATE

edited Feb 13, 2020 at 10:47

answered Feb 13, 2020 at 8:27

Sehdev

5,7123 gold badges14 silver badges34 bronze badges

3 Comments

Egyptionl Over a year ago

your Query look like tis good but not working giv thisSQLSTATE[42000]: Syntax error or access violation

Egyptionl Over a year ago

SQLSTATE[42000]: Syntax error or access violation: 1582 Incorrect parameter count in the call to native function 'str_to_date' (SQL: select * from store_permissions where target_id = 33 and DATE_FORMAT(str_to_date(publish), "%d/%m/%Y") < 12/01/2020 and store_permissions.deleted_at is null)

Sehdev Over a year ago

I have updated the answer. Actually str_to_date takes 2 parameters and I by mistake I passed only 1. Try now

Collectives™ on Stack Overflow

Laravel where dates by specific format

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related