first tuesday of every month ? [message #651619] |
Fri, 20 May 2016 17:27 |
|
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 #651644 is a reply to message #651621] |
Sat, 21 May 2016 15:20 |
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 #651754 is a reply to message #651660] |
Tue, 24 May 2016 08:46 |
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
|
|
|