The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Last day of last completed quarter

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!
0
Best Answer
-
Jim_Knicely - Select Field - Administrator
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)
0
Answers
Does this work?
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?
this works, i read it wrong - we're doing minus 1 of first day of quarter. thank you!