CODEWITHSUNDEEP
sqlsqlitedatetime
Rameez
Rameez

Reputation: 23

Find the number of days in a given month in sqlite3

I am trying to calculate wages data. Is there a way to calculate the number of days in a given month in sqlite3

Upvotes: 2

Views: 818

Answers (3)

forpas
forpas

Reputation: 164099

Assuming that you have the year and the month as a number, you can do it with strftime():

SELECT strftime('%d', '2021' || '-' || printf('%02d', ?) || '-01', '1 month', '-1 day') days

Replace '2021' with the year that you want and ? with the month number.

See the demo.

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94969

Use the DATE function to get from some date to its month's last day, e.g. from '2021-06-05' to '2021-06-30'. Then use STRFTIME to extract that day ('30' in the example). Then cast to INTEGER to get from the day string to a number ('30' -> 30).

SELECT 
  CAST(
    STRFTIME(
      '%d', 
      DATE(
        '2021-06-05',
        'start of month',
        '+1 month',
        '-1 day'
      )
    ) AS INTEGER
  );

Demo: https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=45d8620632c831d554b435b501f98912

Upvotes: 2

Joachim Isaksson
Joachim Isaksson

Reputation: 180947

There are multiple ways to do it, but you can use JULIANDAY to calculate the difference in days between the date in a month and the current date, which should give you the number of days of the current month.

For example to calculate the number of days in the month that contains the date 2010-05-31;

SELECT JULIANDAY('2010-05-31', '+1 month') - JULIANDAY('2010-05-31') days_of_month
> 31

An SQLfiddle to test with.

Upvotes: 3

Related Questions