CODEWITHSUNDEEP
sqldatabasepostgresqlgenerate-series
Amit Sharad
Amit Sharad

Reputation: 1201

Use generate_series to populate list of months for a given year in PostgreSQL

I have the following query that generates list of months for given year when input is date (2014-01-01):

SELECT to_char(dd, 'Month') 
FROM generate_series(CAST('2014-01-01' AS DATE), date_trunc('month', now()), '1 month') as dd;

  to_char  
-----------
 January  
 February 
(2 rows)

Now, instead of 2014-01-01 I need to pass only 2014 and get the same result.

Upvotes: 1

Views: 1496

Answers (2)

Houari
Houari

Reputation: 5651

SELECT distinct to_char(dd, 'Month') FROM generate_series(to_date('2013', 'YYYY') , date_trunc('month', now()), '1 month') as dd;

Upvotes: 2

Tomas Greif
Tomas Greif

Reputation: 22671

You can apped -01-01 to year input:

SELECT 
  to_char(dd, 'Month') 
FROM 
  generate_series(
      ('2013' || '-01-01')::date, 
      ('2013' || '-01-01')::date + interval '1 year' - interval '1 day', 
      '1 month') as dd;

Upvotes: 2

Related Questions