CODEWITHSUNDEEP
sqlpostgresql
java
java

Reputation: 1214

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

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

Upvotes: 1

Views: 3170

Answers (3)

Samy
Samy

Reputation: 11

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)

Upvotes: 1

newman
newman

Reputation: 2719

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

Upvotes: 0

Mureinik
Mureinik

Reputation: 312289

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)

Upvotes: 2

Related Questions