CODEWITHSUNDEEP
laravelpostgresqlcastingeloquent
qasimalbaqali
qasimalbaqali

Reputation: 2131

Query using eloquent whereMonth where date is string

In one of the tables a column with type of varchar contains a date with the following format day-month-year. I would like to run a query using eloquent on that table with whereYear and whereMonth, but I get an error since the column booking_date is not of type Date.

The query I am trying to run is

MyTable::whereYear('booking_date', '=', $year)
        ->whereMonth('booking_date', '=', $month)
        ->get();

And getting the following error

"SQLSTATE[42883]: Undefined function: 7 ERROR:  function pg_catalog.date_part(unknown, character varying) does not exist\nLINE 1: ...\" = $1 and \"said_table\".\"deleted_at\" is null) and extract(ye...\n                                                             ^\nHINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Is there a way to cast the string value to a date before querying it, maybe with using raw expressions? If yes, any hints would be great.

Upvotes: 1

Views: 6121

Answers (2)

Watercayman
Watercayman

Reputation: 8178

If this field is going to be a date on the particular model all the time (and with a name like 'booking_date', it might well be), it is even easier than having to deal with it on every query. You can cast it within the dates field on the model itself:

protected $dates = [
    'booking_date',
];

By default, Eloquent will convert the created_at and updated_at columns to instances of Carbon, and the above will do the same for booking_date. No further casting required. From Laravel docs on date mutators

Upvotes: 1

Shizzen83
Shizzen83

Reputation: 3529

You may easily achieve that thanks to Carbon library which is included within Laravel:

use Carbon\Carbon;

$targetDate = Carbon::now()->year($year)->month($month);
MyTable::whereYear('booking_date', '=', $targetDate)
        ->whereMonth('booking_date', '=', $targetDate)
        ->get();

Upvotes: 0

Related Questions