oracle date question

Not sure I follow but maybe this?

select trunc(to_date('30-may-2012','DD-MON-YYYY'),'d') +6 from dual

My question is if schedule_date is 30-may-2012 then I need to get the sunday before 30-may-2012 and saturday after 30-may-2012.

select to_date('30-may-2012','dd-month-yyyy')  + 6 from dual

note next_day does not include the day of the first parameters.

So,  if that day is Saturday or Sunday, what result would you want?


may 30th is a wednesday, so not a problem there.

what would you expect for June 2, 2012 or June 3, 2012?

select next_day(to_date(:hdr.sched_date,'dd-month-yyyy')-7, 'Sunday'),
           next_day(to_date(:hdr.sched_date,'dd-month-yyyy'), 'Saturday')
    into v_date1,v_date2    
  from dual;

 message('sched date='||:hdr.sched_date);pause;
 message('vdate1='||v_date1);pause;
 message('vdate2='||v_date2);pause;

1. 30-may-12
2. 29-may-2012
3. 04-jun-2012

I am getting these dates instead of 27th may 2012 and 2nd june 2012

is sched_date a date or a string?



if it's a date, don't use to_date on it

date in the format MM/DD/RRRR

also, are you showing the real output above?


I think the problem with gettting 5/29/12 and 6/4/12

 is because those are literally the year 12,  not 2012

and for May-30-0012  those results are correct

so it goes back to the to_date issue.

if it's a date, don't use to_date on it

 If it's a string, you must use the correct format and I HIGHLY recommend not using 2-digit years, you're just begging for errors like this one to come in.  We solved the Y2K bug years ago, don't reinvent it.

>>> date in the format MM/DD/RRRR

that doesn't make sense

dates don't have formats.

strings have formats

the user enters 053012 and I format it to 05/30/2012

when you format it,  are you turning it into a date? or just another string?

and if it's a string that looks like 5/30/2012  then dd-month-yyyy is obviously the wrong format.
In fact, you would get an error because there is no month 30

So,  since you didn't get an error, something in what you've stated above can't really be what you're doing

>>the user enters 053012 and I format it to 05/30/2012<<
I assume the user is entering the value as text. If so, there is no need to format it (i.e. no need for the slashes), you just need to convert it to a date datatype so you can determine the previous Sunday (or the same day if the input is a Sunday).
to_date('053012','mmddyy') converts it to a date.
trunc(to_date('053012','mmddyy'),'ww') then determines the Sunday that is before or on that day.
+6 would then determine the Saturday which follows that Sunday.
SQL> select trunc(to_date('&date','MMDDYY'),'ww') beg_wk
  2  from dual;
Enter value for date: 053012
old   1: select trunc(to_date('&date','MMDDYY'),'ww') beg_wk
new   1: select trunc(to_date('053012','MMDDYY'),'ww') beg_wk

BEG_WK
---------
27-MAY-12

SQL> select trunc(to_date('&date','MMDDYY'),'ww') + 6 end_wk
  2  from dual;
Enter value for date: 053012
old   1: select trunc(to_date('&date','MMDDYY'),'ww') + 6 end_wk
new   1: select trunc(to_date('053012','MMDDYY'),'ww') + 6 end_wk

END_WK
---------
02-JUN-12

assuming your values are actually dates this is what I mean by NOT using to_date

select next_day(:hdr.sched_date-7, 'Sunday'),
           next_day(:hdr.sched_date, 'Saturday')
    into v_date1,v_date2    
  from dual;

thanks