Generic end date of quarter, PostgreSQL

Question

I'd like to produce the generic quarter end date for a given date.

Ex:If I have 2010-01-01, I would like to return 2010-03-31, and so on.

I can get the quarter number and year:

select to_char(date_trunc('quarter',  current_date)::date, 'yyyy-q');

Which returns 2017-3 since today is 2017-07-14

How do I get the quarter end date nicely?

I can get the answer, but its very ugly:

select to_char(date_trunc('year',  date '2015-01-01'),'yyyy') || '-' ||case
    when (select extract('quarter' from date_trunc('quarter', date '2015-01-01')::date )) = 1 then '03-31'
    when (select extract('quarter' from date_trunc('quarter', date '2015-01-01')::date )) = 2 then '06-30'
    when (select extract('quarter' from date_trunc('quarter', date '2015-01-01')::date )) = 3 then '09-30'
    when (select extract('quarter' from date_trunc('quarter', date '2015-01-01')::date )) = 4 then '12-31'
    else '?'
    end

Which returns 2015-03-31 since I put in 2015-01-01.

Is there a better way?

joanolo · Accepted Answer · 2017-07-14T19:34:45.330

I think the shortest and most elegant way to solve this issue is to use date_trunc('quarter',d) (which will retrieve the start of the quarter) + 3 months - 1 day, and use the expression to create a FUNCTION:

CREATE FUNCTION end_of_quarter (d date) 
RETURNS date AS
$$
SELECT
    CAST(date_trunc('quarter', d)  + interval '3 months' - interval '1 day' AS date)
$$
LANGUAGE SQL IMMUTABLE ;

... and then use it:

SELECT
    d, end_of_quarter(d)
FROM
    (SELECT 
        CAST(d0 AS date) AS d 
    FROM 
        generate_series(date '2017-01-01', date '2017-12-31', interval '6 days') AS s(d0)
    ) AS q ;

d           | end_of_quarter
:---------- | :-------------
2017-01-01  | 2017-03-31    
...
2017-03-26  | 2017-03-31    
2017-04-01  | 2017-06-30    
...
2017-06-18  | 2017-06-30    
2017-07-06  | 2017-09-30    
2017-07-12  | 2017-09-30    
...   
2017-09-22  | 2017-09-30    
2017-09-28  | 2017-09-30    
2017-10-04  | 2017-12-31    
2017-10-10  | 2017-12-31    
...  
2017-12-21  | 2017-12-31    
2017-12-27  | 2017-12-31

You can check it at dbfiddle here

You could also shorten a bit your original approach by using the simple version of CASE expression WHEN value THEN ...:

SELECT
    d, 
    extract('year' from d) || '-' ||
    /* case expression when value instead of case when expression */
    case extract('quarter' from d)
        when 1 then '03-31'
        when 2 then '06-30'
        when 3 then '09-30'
        else        '12-31'
    end AS end_of_quarter
FROM
    generate_series(date '2017-01-01', date '2017-12-31', interval '6 days') AS s(d) ;

You can check it at dbfiddle here

If you need to do this very often, define a function:

CREATE FUNCTION end_of_quarter (d date) 
RETURNS date AS
$$
SELECT
    cast (extract('year' from d) || '-' ||
        case extract('quarter' from d)
            when 1 then '03-31'
            when 2 then '06-30'
            when 3 then '09-30'
            else        '12-31'
        end
    AS date)
$$
LANGUAGE SQL IMMUTABLE ;

dbfiddle here

Generic end date of quarter, PostgreSQL

1 Answers1