BigQuery - First/Last Day of Month

First day of a month

There are several ways to find out the first day of a month.

Use DATETIME_TRUNC function

SELECT DATE_TRUNC('2021-05-20', month);

Result: 2021-05-01.

This function can also be used to get the first day of a quarter or a year, etc.

SELECT DATE_TRUNC('2021-05-20', year);

Result: 2021-01-01.

Use DATE_ADD or DATE_SUB function

SELECT DATE_ADD(DATE'2021-05-20', INTERVAL (-1*EXTRACT(DAY FROM DATE'2021-05-20')+1) day);
SELECT DATE_SUB(DATE'2021-05-20', INTERVAL (EXTRACT(DAY FROM DATE'2021-05-20')-1) day);

Result: 2021-05-1.

Last day of a month

Similarly, there are also various ways to find the last day of a month.

Use LAST_DAY function

SELECT LAST_DAY(DATE'2021-05-20');

Result: 2021-05-31.

This function can also be used to find out the last day of the quarter or the year.

SELECT LAST_DAY(DATE'2021-05-20', quarter);
SELECT LAST_DAY(DATE'2021-05-20', year);

Result:

• 2021-06-30
• 2021-12-31

Use DATE_ADD and DATE_SUB function

Alternatively, we can use DATE_ADD and DATE_SUB function to work it out:

SELECT DATE_SUB(DATE_ADD(DATE_TRUNC(DATE'2021-05-20', month),interval  1 month), interval 1 day);

Result: 2021-05-31.