| Code Snippets & Tips

# BigQuery - First/Last Day of Month

visibility 35,544 comment 0 insights toc
more_vert

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

More from Kontext