Last day of the month

Last day of the month

Description

In some SQL dialects there is a LAST_DAY function, but there isn’t one in BigQuery.
We can achieve the same by taking the day before the first day of the following month.

  1. DATE_ADD(<date>, INTERVAL 1 MONTH) sets the reference date to the following month.
  2. DATE_TRUNC(<date>, MONTH) gets the first day of that month.
  3. DATE_SUB(<date>, INTERVAL 1 DAY) gets the day before. <date>-1 can be used as well.

Example: Last day of the current month (local time)

SELECT
DATE_TRUNC(DATE_ADD(CURRENT_DATE('Europe/Madrid'), INTERVAL 1 MONTH), MONTH) - 1

If no time zone is specified as an argument to CURRENT_DATE, the default time zone, UTC, is used.

1 Like

This snippet was featured on the Daily SQL Snippets today:

1 Like

Great tip! :slight_smile: But in BigQuery there is a LAST_DAY Function also:

You can even select what last day you want, LAST DAY of the MONTH, WEEK or YEAR :wink:

Great! Thanks for pointing this out, I didn’t know.
I just found out that this function has been GA since October 2020.

Sure! :slight_smile: Because of those new Stuff in BQ i have subscribed to News from the BigQuery Release Notes :smiley:
Release notes  |  BigQuery  |  Google Cloud Great source for news and often gives an idea how to use a new feature.