Assume I have a table "mytable" with (among others) a DATE column labelled "mydate".
Now I am searching for WHERE clauses for the following filters:
1.) How do I find all rows where the date value represents the last day in month?
2.) How do I find all rows where the date value is the second friday in month?
3.) How do I find all rows where the date value is the 10th of a month?
Thank you
Peter
This should give you what you want. You can change 13 to a larger number if you want additional months.
Code:SQL> SELECT month_num, next_date
2 FROM ( WITH next_12_months AS (
3 SELECT rownum month_num
4 FROM dba_objects
5 WHERE ROWNUM < 13 )
6 SELECT month_num, ADD_MONTHS(TRUNC(SYSDATE, 'MM'), month_num)-1 next_date
7 FROM next_12_months
8 UNION ALL
9 SELECT month_num, NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), (month_num-1))+6, 'FRIDAY') next_date
10 FROM next_12_months
11 WHERE NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), month_num)+6, 'FRIDAY') >= TRUNC(SYSDATE)
12 UNION ALL
13 SELECT month_num, ADD_MONTHS(TRUNC(SYSDATE, 'MM'), (month_num-1))+9 next_date
14 FROM next_12_months
15 WHERE ADD_MONTHS(TRUNC(SYSDATE, 'MM'), month_num)+9 >= TRUNC(SYSDATE) )
16 ORDER BY month_num, next_date;
MONTH_NUM NEXT_DATE
---------- ---------------
1 10-AUG-17
1 11-AUG-17
1 31-AUG-17
2 08-SEP-17
2 10-SEP-17
2 30-SEP-17
3 10-OCT-17
3 13-OCT-17
3 31-OCT-17
4 10-NOV-17
4 10-NOV-17
4 30-NOV-17
5 08-DEC-17
5 10-DEC-17
5 31-DEC-17
6 10-JAN-18
6 12-JAN-18
6 31-JAN-18
7 09-FEB-18
7 10-FEB-18
7 28-FEB-18
8 09-MAR-18
8 10-MAR-18
8 31-MAR-18
9 10-APR-18
9 13-APR-18
9 30-APR-18
10 10-MAY-18
10 11-MAY-18
10 31-MAY-18
11 08-JUN-18
11 10-JUN-18
11 30-JUN-18
12 10-JUL-18
12 13-JUL-18
12 31-JUL-18
36 rows selected.
Elapsed: 00:00:00.19