How to get number of days in month based on date?

Question

Is there a way to use extract from date in format YYYY-MM-DD how many days were in this month?

example:

for 2016-02-05 it will give 29 (Feb 2016 has 29 days)

for 2016-03-12 it will give 31

for 2015-02-05 it will give 28 (Feb 2015 had 28 days)

I'm using PostgreSQL

EDIT:

LAST_DAY function in postgres is not what i'm looking for. it returns DATE while I expect an Integer

Possible duplicate of [LAST\_DAY function in postgres](http://stackoverflow.com/questions/14229038/last-day-function-in-postgres) — sagi, Feb 11 '16 at 08:18
@java Possible duplicate of [How to get the number of days in a month in postgresql](http://stackoverflow.com/questions/6927113/how-to-get-the-number-of-days-in-a-month-in-postgresql) — Vivek S., Feb 11 '16 at 08:24

score 2 · Accepted Answer · answered Feb 11 '16 at 08:19

One way to achieve this would be to subtract the beginning of the following month from the beginning of the current month:

db=> SELECT DATE_TRUNC('MONTH', '2016-02-05'::DATE + INTERVAL '1 MONTH') - 
            DATE_TRUNC('MONTH', '2016-02-05'::DATE);
 ?column? 
----------
 29 days
(1 row)

score 1 · Answer 2 · edited Jun 11 '19 at 14:14

Just needed this today and seems that I came up with pretty much the same as Mureinik, just that I needed it numeric. (PostgreSQL couldn't convert from interval to number directly)

previous month:

select CAST(to_char(date_trunc('month', current_date) - (date_trunc('month', current_date) - interval '1 month'),'dd') as integer)

current month:

select CAST(to_char(date_trunc('month', current_date) + interval '1 month' - date_trunc('month', current_date), 'dd') as integer)

score 0 · Answer 3 · answered Feb 11 '16 at 08:19

You can try next:

SELECT  
  DATE_PART('days', 
    DATE_TRUNC('month', TO_DATE('2016-02-05', 'YYYY-MM-DD')) 
    + '1 MONTH'::INTERVAL 
    - DATE_TRUNC('month', TO_DATE('2016-02-05', 'YYYY-MM-DD'))
  );

Note: there date is used twice. And used convert function TO_DATE

How to get number of days in month based on date?

3 Answers3