CODEWITHSUNDEEP
sqlgoogle-bigquery
Winston
Winston

Reputation: 326

Select where date between given month and day of any year in BigQuery

I have two dates and I want to make a SELECT statement based on the dates. Let's say the dates are 2019-09-14 to 2019-10-15.

I know that I can do the following

SELECT date, amount, name FROM table WHERE name IN ('a', 'b') AND date BETWEEN '2019-09-14' AND '2019-10-15'.

What should I do if I want to instead select all records between Sept 14 to Oct 15 regardless of the year?

Thank you.

Upvotes: 0

Views: 1115

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173028

Another option for BigQuery Standard SQL

select date, amount, name 
from table 
where name in ('a', 'b') 
and date between date(extract(year from date), 9, 14) and date(extract(year from date), 10, 15)

or little less verbose and bigquery'ish version of above

select date, amount, name 
from table, unnest([extract(year from date)]) year 
where name in ('a', 'b') 
and date between date(year, 9, 14) and date(year, 10, 15)

Upvotes: 0

GMB
GMB

Reputation: 222492

Here is one option using simple arithmetics:

select *
from mytable
where 
    name in ('a', 'b')
    and extract(month from date) * 100 + extract(day from date) between 914 and 1015

The idea is to extract separately the month and day, and compare them against a range of numbers. You can also format the date and do string comparison:

select *
from mytable
where 
    name in ('a', 'b')
    and format_date('%m%d', date) between '0914' and '1015'

Upvotes: 2

Related Questions