CODEWITHSUNDEEP
sqlgoogle-bigquery
BrianBeing
BrianBeing

Reputation: 441

BigQuery SQL Query to get First Day of Previous Quarter and Last Day of Previous Quarter

I have a query from Teradata that I am moving to Google BigQuery. Within my WHERE Statement I need data from the Previous Quarter from today. The TeraData is as follows:

WHERE ((visit_date >= TRUNC(ADD_MONTHS(CURRENT_DATE, -3), 'Q')
AND  visit_date <=TRUNC(CURRENT_DATE, 'Q') - 1))

How can I do a similar query within google Big Query? I have tried using

WHERE ((visit_date >= TRUNC(DATE_ADD(CURRENT_DATE, INTERVAL -3 MONTH), 'Q')
AND  visit_date <=TRUNC(CURRENT_DATE, 'Q') - 1))

But this DATE_ADD is not working. Can anyone help?

Upvotes: 0

Views: 2361

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173036

use DATE_TRUNC(DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH), QUARTER) and DATE_TRUNC(CURRENT_DATE, QUARTER) - 1 respectively

so it will be

WHERE ((visit_date >= DATE_TRUNC(DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH), QUARTER)
AND  visit_date <= DATE_TRUNC(CURRENT_DATE, QUARTER) - 1))

Upvotes: 1

Related Questions