SQLite gives us the ability to return the date of the beginning of the month, based on a given date.

This means we can return the date of the first day of the current month, or the first day of the month based on a date that we specify.

This allows us to perform further calculations on the resulting date, like adding a given number of days to it.

Start of the Current Month

Here’s an example that returns the first day of the current month:

SELECT DATE('now', 'start of month');

Result:

2022-03-01

This uses the DATE() function to return the results we want. The 'now' argument returns the current date, and the 'start of month' argument is what modifies it to the beginning of the month.

Start of a Specified Month

But it doesn’t have to be the start of the current month. We can specify any date, and it will return the start of the month, based on that date.

Example:

SELECT DATE('2023-12-10', 'start of month');

Result:

2023-12-01

We can then use the result to perform other actions on it. For example, we can add a given number of days to the result like this:

SELECT DATE('2023-12-10', 'start of month', '+20 days');

Result:

2023-12-21