I know some solutions have already been posted. I will add this one however. Some of
the examples posted have the drawback that they assume your NLS date language is
English.
The formula below uses the fact that 1 January 2003 is a Wednesday.
The expression to find the first Wednesday of the month following test_date. If
test_date is the first Wednesday of the month then the value returned is the first
Wednesday of the following month.
decode (sign (trunc (test_date)
- next_day (last_day (add_months (trunc (test_date), -1)),
to_char (to_date ('20030101', 'YYYYMMDD'), 'DY')
)
),
-1, next_day (trunc (test_date), to_char (to_date ('20030101', 'YYYYMMDD'),
'DY')),
next_day (last_day (trunc (test_date)),
to_char (to_date ('20030101', 'YYYYMMDD'), 'DY')
)
)
proof of concept
SQL> column sort_date noprint
SQL> break on sort_date skip 1
SQL> select
2 trunc (test_date, 'MM') as sort_date,
3 to_char (test_date, 'SYYYY/MM/DD DAY') as test_date,
4 to_char (
5 decode (sign (trunc (test_date)
6 - next_day (last_day (add_months (trunc (test_date), -1)),
7 to_char (to_date ('20030101', 'YYYYMMDD'), 'DY')
8 )
9 ),
10 -1, next_day (trunc (test_date), to_char (to_date ('20030101',
'YYYYMMDD'), 'DY')),
11 next_day (last_day (trunc (test_date)),
12 to_char (to_date ('20030101', 'YYYYMMDD'), 'DY')
13 )
14 )
15 , 'SYYYY/MM/DD DAY') as following_first_wed_of_month
16 from
17 (select to_date ('20030701', 'YYYYMMDD') as test_date from dual
18 union
19 select to_date ('20030702', 'YYYYMMDD') as test_date from dual
20 union
21 select to_date ('20030703', 'YYYYMMDD') as test_date from dual
22 union
23 select to_date ('20030704', 'YYYYMMDD') as test_date from dual
24 union
25 select to_date ('20030705', 'YYYYMMDD') as test_date from dual
26 union
27 select to_date ('20030706', 'YYYYMMDD') as test_date from dual
28 union
29 select to_date ('20030707', 'YYYYMMDD') as test_date from dual
30 union
31 select to_date ('20030708', 'YYYYMMDD') as test_date from dual
32 union
33 select to_date ('20030801', 'YYYYMMDD') as test_date from dual
34 union
35 select to_date ('20030802', 'YYYYMMDD') as test_date from dual
36 union
37 select to_date ('20030803', 'YYYYMMDD') as test_date from dual
38 union
39 select to_date ('20030804', 'YYYYMMDD') as test_date from dual
40 union
41 select to_date ('20030805', 'YYYYMMDD') as test_date from dual
42 union
43 select to_date ('20030806', 'YYYYMMDD') as test_date from dual
44 union
45 select to_date ('20030807', 'YYYYMMDD') as test_date from dual
46 union
47 select to_date ('20030808', 'YYYYMMDD') as test_date from dual
48 union
49 select to_date ('20031001', 'YYYYMMDD') as test_date from dual
50 union
51 select to_date ('20031002', 'YYYYMMDD') as test_date from dual
52 union
53 select to_date ('20031003', 'YYYYMMDD') as test_date from dual
54 union
55 select to_date ('20031004', 'YYYYMMDD') as test_date from dual
56 union
57 select to_date ('20031005', 'YYYYMMDD') as test_date from dual
58 union
59 select to_date ('20031006', 'YYYYMMDD') as test_date from dual
60 union
61 select to_date ('20031007', 'YYYYMMDD') as test_date from dual
62 union
63 select to_date ('20031008', 'YYYYMMDD') as test_date from dual
64 union
65 select to_date ('20031009', 'YYYYMMDD') as test_date from dual)
66 order by 1, 2 ;
TEST_DATE FOLLOWING_FIRST_WED_
-------------------- --------------------
2003/07/01 MARDI 2003/07/02 MERCREDI
2003/07/02 MERCREDI 2003/08/06 MERCREDI
2003/07/03 JEUDI 2003/08/06 MERCREDI
2003/07/04 VENDREDI 2003/08/06 MERCREDI
2003/07/05 SAMEDI 2003/08/06 MERCREDI
2003/07/06 DIMANCHE 2003/08/06 MERCREDI
2003/07/07 LUNDI 2003/08/06 MERCREDI
2003/07/08 MARDI 2003/08/06 MERCREDI
2003/08/01 VENDREDI 2003/08/06 MERCREDI
2003/08/02 SAMEDI 2003/08/06 MERCREDI
2003/08/03 DIMANCHE 2003/08/06 MERCREDI
2003/08/04 LUNDI 2003/08/06 MERCREDI
2003/08/05 MARDI 2003/08/06 MERCREDI
2003/08/06 MERCREDI 2003/09/03 MERCREDI
2003/08/07 JEUDI 2003/09/03 MERCREDI
2003/08/08 VENDREDI 2003/09/03 MERCREDI
2003/10/01 MERCREDI 2003/11/05 MERCREDI
2003/10/02 JEUDI 2003/11/05 MERCREDI
2003/10/03 VENDREDI 2003/11/05 MERCREDI
2003/10/04 SAMEDI 2003/11/05 MERCREDI
2003/10/05 DIMANCHE 2003/11/05 MERCREDI
2003/10/06 LUNDI 2003/11/05 MERCREDI
2003/10/07 MARDI 2003/11/05 MERCREDI
2003/10/08 MERCREDI 2003/11/05 MERCREDI
2003/10/09 JEUDI 2003/11/05 MERCREDI
25 ligne(s) s�lectionn�e(s).
SQL>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
