Skip to content
Advertisement

converting datetime field to date in sql query in laravel contorller

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

Advertisement

Answer

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

  1. $query->whereDate('logs.created_at', '2020-01-25')
  2. $query->whereRaw('DATE(logs.created_at) = ?', ['2020-01-25'])
  3. $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

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement