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