Home » SQL & PL/SQL » SQL & PL/SQL » first tuesday of every month ? (sql developer)
first tuesday of every month ? [message #651619] Fri, 20 May 2016 17:27 Go to next message
nath123
Messages: 19
Registered: May 2016
Location: united states
Junior Member
Hi

Below is the one for first monday of every month in 2016

with t as(
          select  add_months(date '2016-1-1',level-1) dt
            from  dual
            connect by level <= 12
         )
select  dt first_of_the_month,
        trunc(dt + 6,'iw') first_monday_the_month
  from  t


I tried to change the date or level, but couldnot get the first tuesday of everymonth i am trying to understand the concept .?
Re: first tuesday of every month ? [message #651620 is a reply to message #651619] Fri, 20 May 2016 17:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The following method takes the first day of the month that has already been calculated, subtracts 1 day to get the last day of the previous month, then uses next_day and 'tue' to get the next Tuesday.

SCOTT@orcl_12.1.0.2.0> with t as(
  2  	       select  add_months(date '2016-1-1',level-1) dt
  3  		 from  dual
  4  		 connect by level <= 12
  5  	      )
  6  select  dt first_of_the_month,
  7  	     next_day (dt - 1, 'tue') first_tuesday_the_month
  8    from  t
  9  /

FIRST_OF_THE_MO FIRST_TUESDAY_T
--------------- ---------------
Fri 01-Jan-2016 Tue 05-Jan-2016
Mon 01-Feb-2016 Tue 02-Feb-2016
Tue 01-Mar-2016 Tue 01-Mar-2016
Fri 01-Apr-2016 Tue 05-Apr-2016
Sun 01-May-2016 Tue 03-May-2016
Wed 01-Jun-2016 Tue 07-Jun-2016
Fri 01-Jul-2016 Tue 05-Jul-2016
Mon 01-Aug-2016 Tue 02-Aug-2016
Thu 01-Sep-2016 Tue 06-Sep-2016
Sat 01-Oct-2016 Tue 04-Oct-2016
Tue 01-Nov-2016 Tue 01-Nov-2016
Thu 01-Dec-2016 Tue 06-Dec-2016

12 rows selected.

Re: first tuesday of every month ? [message #651621 is a reply to message #651620] Fri, 20 May 2016 17:45 Go to previous messageGo to next message
nath123
Messages: 19
Registered: May 2016
Location: united states
Junior Member
ok, i tried it using NEXT_DAY , thankyou for explaining this
Re: first tuesday of every month ? [message #651644 is a reply to message #651621] Sat, 21 May 2016 15:20 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Keep in mind, NEXT_DAY is NLS dependent and might raise errors or return wrong result. NEXT_DAY in Barbara's solution will raise error for non-english clients:

SQL> alter session set nls_date_language = swedish
  2  /

Session altered.

SQL> with t as(
  2            select  add_months(date '2016-1-1',level-1) dt
  3              from  dual
  4              connect by level <= 12
  5           )
  6  select  dt first_of_the_month,
  7          next_day(dt - 1,'tue') first_tuesday_the_month
  8    from  t
  9  /
        next_day(dt - 1,'tue') first_tuesday_the_month
                        *
ERROR at line 7:
ORA-01846: not a valid day of the week


SQL> 


You could use TRUNC with IW format which is NLS independent:

SQL> with t as(
  2            select  add_months(date '2016-1-1',level-1) dt
  3              from  dual
  4              connect by level <= 12
  5           )
  6  select  dt first_of_the_month,
  7          trunc(dt + 5,'iw') + 1 first_tuesday_the_month
  8    from  t
  9  /

FIRST_OF_ FIRST_TUE
--------- ---------
01-JAN-16 05-JAN-16
01-FEB-16 02-FEB-16
01-MAR-16 01-MAR-16
01-APR-16 05-APR-16
01-MAY-16 03-MAY-16
01-JUN-16 07-JUN-16
01-JUL-16 05-JUL-16
01-AUG-16 02-AUG-16
01-SEP-16 06-SEP-16
01-OCT-16 04-OCT-16
01-NOV-16 01-NOV-16

FIRST_OF_ FIRST_TUE
--------- ---------
01-DEC-16 06-DEC-16

12 rows selected.

SQL> alter session set nls_date_language = swedish
  2  /

Session altered.

SQL> with t as(
  2            select  add_months(date '2016-1-1',level-1) dt
  3              from  dual
  4              connect by level <= 12
  5           )
  6  select  dt first_of_the_month,
  7          trunc(dt + 5,'iw') + 1 first_tuesday_the_month
  8    from  t
  9  /

FIRST_OF_ FIRST_TUE
--------- ---------
01-JAN-16 05-JAN-16
01-FEB-16 02-FEB-16
01-MAR-16 01-MAR-16
01-APR-16 05-APR-16
01-MAJ-16 03-MAJ-16
01-JUN-16 07-JUN-16
01-JUL-16 05-JUL-16
01-AUG-16 02-AUG-16
01-SEP-16 06-SEP-16
01-OKT-16 04-OKT-16
01-NOV-16 01-NOV-16

FIRST_OF_ FIRST_TUE
--------- ---------
01-DEC-16 06-DEC-16

12 rows selected.

SQL> 


SY.
Re: first tuesday of every month ? [message #651660 is a reply to message #651644] Sun, 22 May 2016 16:12 Go to previous messageGo to next message
nath123
Messages: 19
Registered: May 2016
Location: united states
Junior Member
Thankyou Solomon ,for explaining
Re: first tuesday of every month ? [message #651754 is a reply to message #651660] Tue, 24 May 2016 08:46 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
One easy way is to hardcode a date in the following manner that is a tuesday. This will always work no matter what the nls language is. This will always find the first tuesday of the month your in.

select next_day(trunc(sysdate,'mm') - 1, to_char(to_date('03-05-2016', 'dd-mm-yyyy'), 'Day')) next_TUESDAY 
from   dual;

[Updated on: Tue, 24 May 2016 08:49]

Report message to a moderator

Previous Topic: Difference between Global Temporary Table and Collections in PLSQL
Next Topic: Problem while inserting data in unix env
Goto Forum:
  


Current Time: Fri Mar 29 08:07:37 CDT 2024