Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
[how to find first and last date of last, current and next month in BigQuery]
How to find FIRST_DAY, LAST_DAY of current month, next month, previous month in Google Bigquery
Using #Standard SQL
First day of the current month:
```SQL
SELECT DATE_TRUNC(CURRENT_DATE(), MONTH)
```
Last day of the current month (first day next month minus 1):
```SQL
SELECT DATE_SUB(DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH),
INTERVAL 1 DAY)
```
Last day of the previous month (first day current minus 1):
```SQL
SELECT DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY)
```
First day of the next month:
```SQL
SELECT DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH)
```
First date of last month
```SQL
DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH)
```
Last date of last month
```SQL
DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY)
```
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment