This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Mar 8, 2013 8:43 AM by ranit B

# How to get the date for the first monday of each month

Currently Being Moderated
Dear Members,

How to get the date for the first monday of each month.

I have written the following code

SELECT decode (to_char(trunc(sysdate+30 ,'MM'),'DAY'),'MONDAY ',trunc(sysdate+30 ,'MM'),NEXT_DAY(trunc(sysdate+30 ,'MM'), 'MON')) FROM DUAL

But it look bith complex.

Abhishek

Edited by: 9999999 on Mar 8, 2013 4:30 AM
• ###### 1. Re: How to get the date for the first monday of each month
Currently Being Moderated
9999999 wrote:
Dear Members,

How to get the date for the first monday of each month.

ABB
use trunc
``````WITH T AS
(
select trunc(sysdate,'DAY') + LEVEL - 1
from dual
CONNECT BY LEVEL<=31
)

SELECT * FROM T WHERE ROWNUM<=1

``````
Edited by: Rahul India on Mar 8, 2013 6:08 PM
• ###### 2. Re: How to get the date for the first monday of each month
Currently Being Moderated
Rahul India wrote:
9999999 wrote:
Dear Members,

How to get the date for the first monday of each month.

ABB
use trunc
I am using trunc only.
``SELECT decode (to_char(trunc(sysdate+30 ,'MM'),'DAY'),'MONDAY   ',trunc(sysdate+30 ,'MM'),NEXT_DAY(trunc(sysdate+30 ,'MM'), 'MON')) FROM DUAL``
• ###### 3. Re: How to get the date for the first monday of each month
Currently Being Moderated
Hi,
you can use NEXT_DAY function:
``````select next_day(add_months(to_date('1.1.2013', 'dd.mm.yyyy'), level - 1)-1, 'MONDAY') as first_monday
from dual
connect by level <= 12;``````
• ###### 4. Re: How to get the date for the first monday of each month
Currently Being Moderated
solomons solution is good enough ;-)

Edited by: chris227 on 08.03.2013 05:37
• ###### 5. Re: How to get the date for the first monday of each month
Currently Being Moderated
``````ranit@XE11GR2>> select NEXT_DAY(sysdate - to_char(sysdate,'dd')+1,'monday') from dual;

NEXT_DAY(
---------
04-MAR-13``````
``````ranit@XE11GR2>> with xx as(
2     select add_months(to_date('1/1/2013','dd/mm/yyyy'),level-1) dt from dual
3     connect by level <=12
4  )
5  select NEXT_DAY(dt - to_char(dt,'dd')+1,'monday') from xx;

NEXT_DAY(
---------
07-JAN-13
04-FEB-13
04-MAR-13
08-APR-13
06-MAY-13
03-JUN-13
08-JUL-13
05-AUG-13
02-SEP-13
07-OCT-13
04-NOV-13
02-DEC-13

12 rows selected.``````
• ###### 6. Re: How to get the date for the first monday of each month
Currently Being Moderated
ranit B wrote:
``````ranit@XE11GR2>> select NEXT_DAY(sysdate - to_char(sysdate,'dd')+1,'monday') from dual;

NEXT_DAY(
---------
04-MAR-13``````
It wont work for April
• ###### 7. Re: How to get the date for the first monday of each month
Currently Being Moderated
``````WITH T AS
(
select trunc(sysdate,'DAY') + LEVEL - 1
from dual
CONNECT BY LEVEL<=31
)

SELECT * FROM T WHERE ROWNUM<=1``````
try this
• ###### 8. Re: How to get the date for the first monday of each month
Currently Being Moderated
Hi,

If dt is a DATE
``````NEXT_DAY ( TRUNC (dt, 'MONTH') - 1
, 'Monday'
)``````
is the first Monday in the same month as dt.

The expression above depends on NLS_DATE_LANGUAGE. For something independent of NLS settings:
``````NEXT_DAY ( TRUNC (dt, 'MONTH') - 1
, TO_CHAR ('2013-03-04', 'Day')
)``````
There's nothing magical about March 4, 2013; any Monday in any year will do as well.
• ###### 9. Re: How to get the date for the first monday of each month
Currently Being Moderated
Hi Frank,

I'm trying to get rid of this 'April - Monday' but not able to. Can you please check this once?
``````ranit@XE11GR2>> with xx as(
2         select add_months(to_date('1/1/2013','dd/mm/yyyy'),level-1) dt from dual
3         connect by level <=12
4  )
5  select
6     DECODE(to_char(dt,'day'),'monday',
7             dt, -- "why this is not working?"
8             NEXT_DAY(dt - to_char(dt,'dd')+1,'monday'))
9  from xx;

DECODE(TO_CHAR(DT,
------------------
07-JAN-13
04-FEB-13
04-MAR-13
08-APR-13
06-MAY-13
03-JUN-13
08-JUL-13
05-AUG-13
02-SEP-13
07-OCT-13
04-NOV-13
02-DEC-13

12 rows selected.``````
• ###### 10. Re: How to get the date for the first monday of each month
Currently Being Moderated
Use IW format - it will make solution NLS independent. And all you need is truncate 7<sup>th</sup> day of each month using IW:
``````select  sysdate current_date,
trunc(trunc(sysdate,'mm') + 6,'iw') first_monday_the_month
from  dual
/

CURRENT_D FIRST_MON
--------- ---------
08-MAR-13 04-MAR-13

SQL> ``````
Below is list of first monday of the month for this year:
``````with t as(
from  dual
connect by level <= 12
)
select  dt first_of_the_month,
trunc(dt + 6,'iw') first_monday_the_month
from  t
/

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

FIRST_OF_ FIRST_MON
--------- ---------
01-DEC-13 02-DEC-13

12 rows selected.

SQL> ``````
SY.
• ###### 11. Re: How to get the date for the first monday of each month
Currently Being Moderated
``````ranit@XE11GR2>> with xx as(
2     select add_months(to_date('1/1/2013','dd/mm/yyyy'),level-1) dt from dual
3     connect by level <=12
4  )
5  select NEXT_DAY(dt - to_char(dt,'dd')+1,'monday') from xx;

NEXT_DAY(
---------
07-JAN-13
04-FEB-13
04-MAR-13
*08-APR-13* -- Incorrect
06-MAY-13
03-JUN-13
*08-JUL-13* -- Incorrect
05-AUG-13
02-SEP-13
07-OCT-13
04-NOV-13
02-DEC-13

12 rows selected.``````
08-JUL-13 & 08-APR-13 is incorrect. You can use it the way Ivan did, subtract 1 to reach last day of prev month and then use NEXT_DAY to get the first monday of nect month. i.e use NEXT_DAY(dt - 1,'monday')

Edited to indicate April is also incorrect
• ###### 12. Re: How to get the date for the first monday of each month
Currently Being Moderated
Yes, i got that logic now.

But why is my second one decode not working?
• ###### 13. Re: How to get the date for the first monday of each month
Currently Being Moderated
It nor working as the logic is incorrect.
``NEXT_DAY(dt - to_char(dt,'dd')+1,'monday')``
For 1-Jul-13 which is a monday. it will work out as next_day(1-Jul-13 - 1 + 1,'monday'). So you have the date(1st day of the month) as monday and you are trying to find the next monday, which will be the 2nd monday. 1st April 2013 is also a monday.
• ###### 14. Re: How to get the date for the first monday of each month
Currently Being Moderated
For 1-Jul-13 which is a monday. it will work out as next_day(1-Jul-13 - 1 + 1,'monday'). So you have the date(1st day of the month) as monday and you are trying to find the next monday, which will be the 2nd monday. 1st April 2013 is also a monday.
That is the reason I added a DECODE
1 2 Previous Next

#### Legend

• Correct Answers - 10 points