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?
Best Answer
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 aFUNCTION
:... and then use it:
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 ...
:You can check it at dbfiddle here
If you need to do this very often, define a function:
dbfiddle here