Forum Stats

• 3,769,300 Users
• 2,252,946 Discussions

Discussions

SQL Calendar query

Member Posts: 131 Red Ribbon
edited Oct 19, 2021 9:57AM

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

Tagged:

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

```select next_day(add_months(trunc(sysdate,'MM'),level-1)-1,'Wed')+18
from dual connect by level <= 24

------------------------------------------------------------
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
.....
```
• Member, Moderator Posts: 41,223 Red Diamond

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.

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

```select next_day(add_months(trunc(sysdate,'MM'),level-1)-1,'Wed')+18
from dual connect by level <= 24

------------------------------------------------------------
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
.....
```
• Member Posts: 131 Red Ribbon

Thank you so much Cormaco :)

• Member, Moderator Posts: 41,223 Red Diamond

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.