Raw query with strftime

Hi,

I have a hard time compiling the DB:: code for a query that involves a raw where statement. The raw query looks like this:

select * from texts where strftime('%Y-%m', updated_at) = 2018-02;

So far, I've come up with the knowledge that using this strftime(...) section in the following segment works perfectly fine:

DB::table('texts')->select(DB::raw("strftime('%Y-%m', updated_at) as y"))->get();

It does not however work when I attempt to convert it into a where clause like so:

DB::table('texts')->where(DB::raw("strftime('%Y-%m', updated_at) = '2016-1'"))->get();

Please help!

Best Answer

Level 88

Great question! I think you need to use something like!

$date = '2016-1';

DB::table('texts')->whereRaw("strftime('%Y-%m', created_at) = {$date}")->get();

I think DB::raw works a bit different, because of the strftime call. It works with whereRaw, but not with DB::raw really strange...

Please or to participate in this conversation.