CODEWITHSUNDEEP
mysqlgoogle-bigquery
vicky bundele
vicky bundele

Reputation: 1

Big Query to Extract data for last one year

I need to extract data from big query for one year provided it should take data for past 12 months from last day of the last month for example todays date is 27/10/2022 .It should extract data from 01/10/2021 to 30/9/2022.

I tried WHERE DATE(xyz) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH) AND CURRENT_DATE(). Its taking from current date but i need it 12 months past from last day of previous month

Upvotes: 0

Views: 752

Answers (1)

Georg Richter
Georg Richter

Reputation: 7516

The last day of last month can be calculated by

SELECT LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH);
+---------------------------------------------+
| LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)) |
+---------------------------------------------+
| 2022-09-30                                  |
+---------------------------------------------+

To specify the beginning of the period, subtract 13 MONTH instead of 1 MONTH and add one day:

... WHERE DATE BETWEEN DATE_ADD(LAST_DAY(DATE_SUB(now(), INTERVAL 13 MONTH)), INTERVAL 1 DAY) AND LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH);

Upvotes: 0

Related Questions