how to check if date is within the same month as date in another column SQL BQ

I have two date fields in my BQ table. One is start_date and the other one is end_date. DataType for both columns is: DATE. Data looks like this:

start_date           end_date
2022-02-28           2022-02-28
2022-02-28           2022-03-01
2022-03-01           2022-03-02
2022-03-01           2022-04-01

I need to create a new column and check if both dates are within the same month. The values would be “yes” and “no”. Yes if two dates are within the same month, no if not. This is desired output:

start_date           end_date              outcome
2022-02-28           2022-02-28              yes
2022-02-28           2022-03-01              no
2022-03-01           2022-03-02              yes
2022-03-01           2022-04-01              no

I tried:

select
    case
        when (DATE(start_date)) = (
            DATE(end_date),
            INTERVAL 1 MONTH
        )
    ) then 'yes'
    else 'no'
end as outcome
FROM
    my_bq_table

Got an error: No matching signature for operator = for argument types: DATE, STRUCT<DATE, INTERVAL>. Supported signature: ANY = ANY

Thank you in advance!

Answer

I’d propose using ‘extract’ to accomplish this task. Please see below.

Schema (MySQL v8.0)

CREATE TABLE Date_Table (
  `start_date` DATE,
  `end_date` DATE
);

INSERT INTO Date_Table
  (`start_date`, `end_date`)
VALUES
  ('2022-02-28', '2022-02-28'),
  ('2022-02-28', '2022-03-01'),
  ('2022-03-01', '2022-03-02'),
  ('2022-03-01', '2022-04-01');

---

Query #1

select *,
case when extract(month from start_date) = extract(month from end_date) then "Yes" else "No" End as Same_Month 
from Date_Table;

start_date	end_date	Same_Month
2022-02-28	2022-02-28	Yes
2022-02-28	2022-03-01	No
2022-03-01	2022-03-02	Yes
2022-03-01	2022-04-01	No

---

View on DB Fiddle