CODEWITHSUNDEEP
phpmysqllaravel
cookeemonster27
cookeemonster27

Reputation: 23

Laravel 7: getting rows between two dates where the column is datetime

I'm having trouble building an eloquent query wherein I can recreate this sql command: The date_created column is datetime.

SELECT * FROM covidchecklist cc 
LEFT JOIN patient_appointment pa ON cc.appointment_id = pa.appointment_id 
LEFT JOIN users pat ON pa.user_id = pat.id 
WHERE DATE_FORMAT(cc.date_created,'%Y-%m-%d') BETWEEN '2021-01-27' AND '2021-01-27'

What I have right now:

CovidChecklist::leftJoin('patient_appointment', 'covidchecklist.appointment_id', '=', 'patient_appointment.appointment_id')
    ->leftJoin('users as patient', 'patient_appointment.user_id', '=', 'patient.id')
    ->whereRaw(' DATE_FORMAT(covidchecklist.date_created,"%Y-%m-%d") BETWEEN '.'$this->date_from'.' AND '.'$this->date_to'.' ')
    ->select(
        *select stuff*
     )
    ->get();

I am using whereRaw since I can't get this to work:

->whereBetween('covidchecklist.date_created', [$this->date_from, $this->date_to])

Upvotes: 1

Views: 74

Answers (1)

Aulia Wiguna
Aulia Wiguna

Reputation: 416

Try this, mate

->whereBetween(\DB::raw('DATE_FORMAT(covidchecklist.date_created,"%Y-%m-%d")'), [$this->date_from, $this->date_to])

Upvotes: 1

Related Questions