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!

Tagged:

Best Answer

  • Jim_KnicelyJim_Knicely Administrator
    Accepted Answer

    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)
    
    

Answers

  • Jim_KnicelyJim_Knicely Administrator

    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)
    
    
  • @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?

  • @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!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.