access_time 25 days ago languageEnglish
more_vert

BigQuery - First/Last Day of Month

visibility 20 comment 0

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.

info Last modified by Raymond 25 days ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn.

Want to contribute on Kontext to help others?

Learn more

More from Kontext

visibility 846
thumb_up 0
access_time 9 months ago