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