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.