How to extract the Day, Month and Year from the date?

Question

I just cant figure out how to get the day, the months and the year from the dates.

Here it is the eloquent

 public function query(Detail $model)
    {
    return $model->newQuery()->leftjoin('fishers','fishers.id', '=','details.fisher_id')
    ->leftjoin('species','species.id', '=', 'details.species_id')
    ->leftjoin('purposes','purposes.id', '=', 'details.purpose_id')
    ->leftjoin('islands','islands.id', '=', 'fishers.island_id')
    ->leftjoin('preservations','preservations.id', '=', 'details.preservation_id')   
    ->select('fishers.*','details.*','details.indate','islands.island_name','fishers.fisher_first_name','fishers.fisher_last_name','details.weight','species.species_name','purposes.purpose_name','preservations.preservation_name');
   }

I have tried to use Month(details.indate) as Month, Year(details.indate) as Year but seems not working. I used date as the datatype for indate and when I do Month(details.indate) as Month I get this error:

SQLSTATE[42S22]: COLUMN NOT FOUND: 1054 UNKNOWN COLUMN 'Month(details.indate)' IN 'field list' (SQL: SELECT fishers.* , details.* , MONTH(details.indate) AS MONTH

This is my datatable columns

   protected function getColumns()
    {
        return [
            [ 'data' => 'purpose_name', 'name' => 'purposes.purpose_name', 'title' => 'Purpose' ],
            [ 'data' => 'fisher_first_name', 'name' => 'fishers.fisher_first_name', 'title' => 'Fisher Name' ],
            [ 'data' => 'preservation_name', 'name' => 'preservations.preservation_name', 'title' => 'Preservation Methods' ],
            [ 'data' => 'species_name', 'name' => 'species.species_name', 'title' => 'Species Name' ],
            [ 'data' => 'island_name', 'name' => 'islands.island_name', 'title' => 'Island Name' ],
            [ 'data' => 'weight', 'name' => 'details.weight', 'title' => 'Weight' ],
            [ 'data' => 'indate', 'name' => 'details.indate', 'title' => 'Month' ],
            [ 'data' => 'indate', 'name' => 'details.indate', 'title' => 'Year' ],

        ];
    }

can someone help me how to do it?

What is the datatype for details.indate? Also what did it return when you do Month(details.indate) as Month, Year(details.indate) as Year? — FaNo_FN
I used date as the datatype for indate and when I do Month(details.indate) as Month I get this error SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Month(details.indate)' in 'field list' (SQL: select fishers.*, details.*, Month(details.indate) as Month, — Kairaoi T
So if you do without using MONTH() or YEAR() will the query work? — FaNo_FN
FYI you are using query builder, not Eloquent. You'll need to use raw methods if you want to use MySQL functions like MONTH() etc in your query. — Don't Panic

D Malan D Malan · Accepted Answer · 2020-01-13T13:29:28

You need to use the DB::raw function to select expressions using Laravel Eloquent.

For example:

return $model->newQuery()->...
->select(DB::raw('Month(details.indate) as Month'), DB::raw('Year(details.indate) as Year'), 'fishers.*','details.*', ...)

Be sure to add use DB; at the top of your file.

How to extract the Day, Month and Year from the date?

1 Answers