languageEnglish

BigQuery - First/Last Day of Month

visibility 686 comment 0 access_time 5 months ago

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 5 months ago copyright This page is subject to Site terms.

Subscribe newsletter

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

More from Kontext

visibility 272
thumb_up 0
access_time 7 months ago
visibility 97
thumb_up 0
access_time 5 months ago
visibility 2388
thumb_up 0
access_time 3 years ago