SQL Calendar query

Dimpy

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

cormaco

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    
.....

Frank Kulash

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.

cormaco

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

Thank you so much Cormaco :)

Frank Kulash

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.