Forum Stats

  • 3,769,300 Users
  • 2,252,946 Discussions
  • 7,874,982 Comments

Discussions

SQL Calendar query

Dimpy
Dimpy Member Posts: 131 Red Ribbon
edited Oct 19, 2021 9:57AM in SQL & PL/SQL

HI,

Help me to write below query:

To find the date of Sunday after the 3rd Wednesday for next 2 years

Sunday after the 3rd Wednesday.

The output will be like this Sunday after the 3rd Wednesday

OCT-24

NOV-21

DEC-19 till next two years.

Thanks!!

Best Answers

  • cormaco
    cormaco Member Posts: 1,722 Bronze Crown
    edited Oct 19, 2021 10:30AM Accepted Answer

    Here is one way (adjust the dayname to your language setting):

    select next_day(add_months(trunc(sysdate,'MM'),level-1)-1,'Wed')+18
    from dual connect by level <= 24 
    
    NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,'MM'),LEVEL-1)-1,'WED')+18
    ------------------------------------------------------------
    24.10.21                                                    
    21.11.21                                                    
    19.12.21                                                    
    23.01.22                                                    
    20.02.22                                                    
    20.03.22                                                    
    24.04.22                                                    
    22.05.22                                                    
    19.06.22                                                    
    24.07.22                                                    
    21.08.22    
    .....
    
    Dimpy
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond
    Accepted Answer

    Hi, @Dimpy

    Here's a way that will work no matter what your NLS settings are:

    SELECT  TRUNC ( ADD_MONTHS ( TRUNC (SYSDATE, 'MONTH')
    	 	 	   , LEVEL - 1
    			   ) + 25
    	 	, 'IW'
    		) - 1   AS wed_3_sun
    FROM	 dual
    CONNECT BY LEVEL <= 12 * 2 -- last number is number of years
    ;
    

    Here's how it works:

    • The third Wednesday of each month is the Wednesday between the 15th and the 21st of the month, inclusive.
    • The MONDAY after the third is Monday between the 20th and the 26th of the month, inclusive. This is what TRUNC (dt, 'IW') returns.
    • The SUNDAY after the third Wednesday is one day before that Monday.


    Dimpy

Answers

  • cormaco
    cormaco Member Posts: 1,722 Bronze Crown
    edited Oct 19, 2021 10:30AM Accepted Answer

    Here is one way (adjust the dayname to your language setting):

    select next_day(add_months(trunc(sysdate,'MM'),level-1)-1,'Wed')+18
    from dual connect by level <= 24 
    
    NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,'MM'),LEVEL-1)-1,'WED')+18
    ------------------------------------------------------------
    24.10.21                                                    
    21.11.21                                                    
    19.12.21                                                    
    23.01.22                                                    
    20.02.22                                                    
    20.03.22                                                    
    24.04.22                                                    
    22.05.22                                                    
    19.06.22                                                    
    24.07.22                                                    
    21.08.22    
    .....
    
    Dimpy
  • Dimpy
    Dimpy Member Posts: 131 Red Ribbon

    Thank you so much Cormaco :)

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond
    Accepted Answer

    Hi, @Dimpy

    Here's a way that will work no matter what your NLS settings are:

    SELECT  TRUNC ( ADD_MONTHS ( TRUNC (SYSDATE, 'MONTH')
    	 	 	   , LEVEL - 1
    			   ) + 25
    	 	, 'IW'
    		) - 1   AS wed_3_sun
    FROM	 dual
    CONNECT BY LEVEL <= 12 * 2 -- last number is number of years
    ;
    

    Here's how it works:

    • The third Wednesday of each month is the Wednesday between the 15th and the 21st of the month, inclusive.
    • The MONDAY after the third is Monday between the 20th and the 26th of the month, inclusive. This is what TRUNC (dt, 'IW') returns.
    • The SUNDAY after the third Wednesday is one day before that Monday.


    Dimpy