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.
$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