RE: Job to run first Wednesday

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 22 Jul 2003 16:29:12 -0700
Message-Id: <25988.338994@fatcity.com>

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

Received on Tue Jul 22 2003 - 18:29:12 CDT