CODEWITHSUNDEEP
sqlgoogle-bigquerypartition
markdg
markdg

Reputation: 1

SQL Query Across Partitioned Database (by Day)

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.

Upvotes: 1

Views: 52

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Another option:

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

Upvotes: 1

markdg
markdg

Reputation: 1

Ok I figured it out.

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

Upvotes: 1

Related Questions