whereIn "year" from date column

Hello,

How can I select all the rows with specific years provided by a years array from a date column?

For example, I have the following data:

id  |  date
----|-----------
 1  | 2020-01-01
 2  | 2020-07-07
 3  | 2021-01-01
 4  | 2022-01-01
 5  | 2023-01-01

and I am given array of years: ['2020', '2022'], I want to get the rows whos date has these years so in this case, rows 1, 2 and 4.

I tried:

$data = DB::table('table')->whereIn('YEAR(date)', $years_array)->get();

But that doesn't work

Thanks

tisuchi

2 years ago

Level 70

@ligonsker You can try this:

$years = ['2020', '2022'];

$data = DB::table('table')
          ->where(function ($query) use ($years) {
              foreach ($years as $year) {
                  $query->orWhereRaw('YEAR(date) = ?', [$year]);
              }
          })->get();

2 likes

Ligonsker

2 years ago

Level 8

@tisuchi thank you! that worked perfectly and it is even with bindings

1 like

tisuchi

2 years ago

Level 70

@Ligonsker Good to know that 🙂

1 like

Please or to participate in this conversation.