3

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?

  • From your answer(s), this is what I wanted: CAST(date_trunc('quarter', d) + interval '3 months' - interval '1 day' AS date) Big help! – mountainclimber Jul 14 '17 at 18:58
  • Complete aside: a date without a time zone is like a length within a unit. Make sure your date has a time zone. – jpmc26 Jul 14 '17 at 20:24
10

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.