SQL Query Across Partitioned Database (by Day)-CodePudding

So in the past 3 months, we have gone from a Google Sheets with 5 tabs, up to a connected BigQuery DB referencing the Google Sheets with 5 tables and writing queries. Today, we just upgraded again to a full daily partitioned database.

I am struggling to figure out how to write my queries across multiple days of data.

When I go to start the query it defaults to today.

SELECT order_number 
FROM `project-123456.client_name.orders` 
WHERE DATE(submitted_date) = "2022-02-10" 
LIMIT 1000

I am trying to figure out the syntax for the month of January for example (and I know this isn't right)

WHERE DATE(submitted_date) = Jan 1 - Jan 31.

Any suggestions would be great, I am learning SQL at an alarming pace but in this case, I think I just don't know the right question to ask.

CodePudding user response：

Ok I figured it out.

WHERE DATE(submitted_date) >= "2022-01-01" AND DATE(submitted_date) <= "2022-01-31"

CodePudding user response：

another option:

where date_trunc(date(submitted_date), month) = '2022-01-01'