converting datetime field to date in sql query in laravel contorller

Question

i have to execute an sql query into laravel controller the query inclueds where caluse in which it compares a field from database which is in datetime type with another field that i get from the blade which has a date type, so i have to convert the database field from datetime type to date type

this is my query in laravel controller, i have tried dateformat but id doesnt work

 $querydate = $request->get('querydate');
 $data = DB::table('logs')

        ->select('logs.user_id','logs.NUMCCP','logs.created_at') 

           ->where  (DB::raw('(DATE_FORMAT(logs.created_at,"%Y-%m-%d"))'),$querydate)
           ->orderby('logs.created_at','desc') 
        ->get();

$querydate is in yyyy-mm-dd format, logs.created_at is in datetime type and $querydate is in date type so i'm trying frist to convert logs.created_at into a date type after that i wanna get the information when logs.created_at equals to $querydate

i have tried several syntaxes i found on the net but they didnt work for example: ->whereRaw("DATE_FORMAT(logs.created_at, '%Y-%m-%d') = $querydate") ->where(DB::raw("(DATE_FORMAT(logs.created_at,'%Y-%m-%d'))"),$querydate) ->orWhere('CAST(logs.created_at AS DATE)', '=', $querydate) none of these is working

You can simplify it to DATE(logs.created_at) instead of using DATE_FORMAT. — aynber
What about the query isn't working? Is it giving an error or just not returning any data? — aynber
i had tried a static data instead of query somthing like this ``` ->where (DB::raw('(DATE_FORMAT(logs.created_at,"%Y-%m-%d"))'),'2018-02-18')``` — Hadjer Rezkallah

Malkhazi Dartsmelidze Malkhazi Dartsmelidze · Accepted Answer · 2020-02-25T14:30:45

I think logs.created must be timestamp and laravel has several ways to select and filter by given date.

$query->whereDate('logs.created_at', '2020-01-25')
$query->whereRaw('DATE(logs.created_at) = ?', ['2020-01-25'])
$query->where(DB::raw('DATE(logs.created_at)'), '2020-01-25')

mysql DATE function converts date from given timestamp (or string) with default format Y-m-d or yyyy-mm-dd

Hope this helps you

converting datetime field to date in sql query in laravel contorller

2 Answers