Last day of last completed quarter

pratheek238

Hello - How can i get last day of the last completed quarter?

Quarter in our world quarter is defined as below:
Q1: 01/01 - 03/31
Q2: 04/01 - 06/30
Q3: 07/01 - 09/30
Q4: 10/01 - 12/31

Let's say today's date is 11/16/2021, my query should pull 09/30
if today's date is 05/21/2021, my query should pull 03/31

any help is greatly appreciated! Thanks!

Jim_Knicely

This isn't right?

dbadmin=> SELECT current_date today, (TRUNC('01/12/2022'::DATE, 'Q')-1)::DATE last_day_of_qtr;
   today    | last_day_of_qtr
------------+-----------------
 2021-11-16 | 2021-12-31
(1 row)

Jim_Knicely

Does this work?

dbadmin=> SELECT current_date today, (TRUNC(current_date, 'Q')-1)::DATE last_day_of_qtr;
   today    | last_day_of_qtr
------------+-----------------
 2021-11-16 | 2021-09-30
(1 row)

dbadmin=> SELECT current_date today, (TRUNC('05/21/2021'::DATE, 'Q')-1)::DATE last_day_of_qtr;
   today    | last_day_of_qtr
------------+-----------------
 2021-11-16 | 2021-03-31
(1 row)

pratheek238

@Jim_Knicely said:
Does this work?

dbadmin=> SELECT current_date today, (TRUNC(current_date, 'Q')-1)::DATE last_day_of_qtr;
   today    | last_day_of_qtr
------------+-----------------
 2021-11-16 | 2021-09-30
(1 row)

dbadmin=> SELECT current_date today, (TRUNC('05/21/2021'::DATE, 'Q')-1)::DATE last_day_of_qtr;
   today    | last_day_of_qtr
------------+-----------------
 2021-11-16 | 2021-03-31
(1 row)

This should work for all quarters except Q1

If today's date is 01/12/2022, then i don't think above logic would work since it would basically do 1-1> 01/12/2022 is Q1 so 1 in numerical value. Makes sense?

pratheek238

@Jim_Knicely said:
This isn't right?

dbadmin=> SELECT current_date today, (TRUNC('01/12/2022'::DATE, 'Q')-1)::DATE last_day_of_qtr;
   today    | last_day_of_qtr
------------+-----------------
 2021-11-16 | 2021-12-31
(1 row)

this works, i read it wrong - we're doing minus 1 of first day of quarter. thank you!