smart000 wrote:Don't use rtrim, use FM format modifier:
to_char(dt,'fmday')Thanks SY... it worked!!!
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,'fmday'),'monday', 7 dt, 8 NEXT_DAY(dt - to_char(dt,'dd')+1,'monday')) 9 from xx; DECODE(TO --------- 07-JAN-13 04-FEB-13 04-MAR-13 01-APR-13 06-MAY-13 03-JUN-13 01-JUL-13 05-AUG-13 02-SEP-13 07-OCT-13 04-NOV-13 02-DEC-13 12 rows selected.
smart000 wrote:Not so 'smart' ;) My logic was correct dude.
It nor working as the logic is incorrect.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.
NEXT_DAY(dt - to_char(dt,'dd')+1,'monday')