2 Ways to Get the Number of Days in a Month in SQLite

Here are two options for returning the number of days in a given month in SQLite. This could be the number of days in the current month, or it could be based on a given date.

Option 1

We can use the JULIANDAY() function to calculate the difference between the date and the date plus one month:

SELECT JULIANDAY('2030-07-15', '+1 month') - JULIANDAY('2030-07-15');

Result:

31.0

We can also use the CAST() function to cast the result as an integer:

SELECT 
    CAST(
        JULIANDAY('2030-07-15', '+1 month') - JULIANDAY('2030-07-15')
        AS INTEGER
        );

Result:

Option 2

The second option involves using the STRFTIME() function and the DATE() function:

SELECT
    STRFTIME(
        '%d', 
        DATE(
            '2035-02-21',
            'start of month',
            '+1 month',
            '-1 day'
        )
  );

Result:

Here, I used DATE() to get the last day of the month. I then used STRFTIME() to format that date to the day of the month ('%d' formats it to the day of the month).

We can also use the CAST() function to cast the result as an integer:

SELECT 
    CAST(
        STRFTIME(
            '%d', 
            DATE(
            '2035-02-21',
            'start of month',
            '+1 month',
            '-1 day'
            )
    ) AS INTEGER
  );

Result:

Database Example

This example uses data from a database:

SELECT
    InvoiceDate,
    CAST(JULIANDAY(InvoiceDate, '+1 month') - JULIANDAY(InvoiceDate) AS INTEGER) AS "Days in Month" 
FROM Chinook.Invoice 
WHERE customerid = 2;

Result:

+---------------------+---------------+
|     InvoiceDate     | Days in Month |
+---------------------+---------------+
| 2009-01-01 00:00:00 | 31            |
| 2009-02-11 00:00:00 | 28            |
| 2009-10-12 00:00:00 | 31            |
| 2011-05-19 00:00:00 | 31            |
| 2011-08-21 00:00:00 | 31            |
| 2011-11-23 00:00:00 | 30            |
| 2012-07-13 00:00:00 | 31            |
+---------------------+---------------+