In SQLite, we can use the following method to calculate the last day of a given month.
We can base this either on the current date, or on some other specific date.
Last Day of the Current Month
Here’s an example that returns the last day of the current month:
SELECT DATE('now', 'start of month', '+1 month', '-1 day');
Result:
2022-03-31
This uses the DATE()
function to return the results we want. The 'now'
argument returns the current date, and the 'start of month'
argument modifies it to the beginning of the month. We then use '+1 month'
to add a month to the month’s beginning (which would shift it forward to the beginning of the following month), then we reduce that date by a day (to bring it back to the end of the initial month).
Last Day of a Specified Month
But as mentioned, we aren’t limited to the end of just the current month. We can specify any date, and it will return the end of the month, based on that date.
Here are some examples:
SELECT
DATE('2023-02-10', 'start of month', '+1 month', '-1 day') AS Feb,
DATE('2023-11-10', 'start of month', '+1 month', '-1 day') AS Nov,
DATE('2023-12-10', 'start of month', '+1 month', '-1 day') AS Dec;
Result:
Feb Nov Dec ---------- ---------- ---------- 2023-02-28 2023-11-30 2023-12-31
We can see that SQLite is smart enough to work out how many days are in each month. When we use '+1 month'
, SQLite knows how many days are in each month. We can see that February has 28 days, November 30, and December has 31.
If we shift the original date forward to 2024, we can see that February has 29 days in that year:
SELECT
DATE('2024-02-10', 'start of month', '+1 month', '-1 day') AS Feb,
DATE('2024-11-10', 'start of month', '+1 month', '-1 day') AS Nov,
DATE('2024-12-10', 'start of month', '+1 month', '-1 day') AS Dec;
Result:
Feb Nov Dec ---------- ---------- ---------- 2024-02-29 2024-11-30 2024-12-31