BigQuery - First/Last Day of Month

visibility 5,804 access_time 2 years ago languageEnglish

First day of a month

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


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);


  • 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.

info Last modified by Raymond 2 years ago copyright This page is subject to Site terms.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

timeline Stats
Page index 15.76
More from Kontext
BigQuery SQL - COALESCE and IFNULL Functions
visibility 770
thumb_up 0
access_time 2 years ago
Convert varchar to date in SQL / Teradata
visibility 8,733
thumb_up 1
access_time 3 years ago
Get List of Tables/Views and Schema in BigQuery
visibility 3,971
thumb_up 1
access_time 2 years ago