oracle date question

anumoses
anumoses used Ask the Experts™
on
select trunc(sysdate,'d') from dual

5/20/2012


select trunc(sysdate,'d') +6 from dual

5/26/2012

Instead of sysdate I need to add a schedule_date like 30-may-2012. How can I get that?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Not sure I follow but maybe this?

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

Author

Commented:
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.
Most Valuable Expert 2011
Top Expert 2012

Commented:
select to_date('30-may-2012','dd-month-yyyy')  + 6 from dual
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2011
Top Expert 2012
Commented:
>> 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.

then why didn't you ask for that?


select next_day(to_date('30-may-2012','dd-month-yyyy')-7, 'Sunday') previous_sunday,
next_day(to_date('30-may-2012','dd-month-yyyy'), 'Saturday')  following_saturday from dual
Most Valuable Expert 2011
Top Expert 2012

Commented:
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?

Author

Commented:
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
Most Valuable Expert 2011
Top Expert 2012

Commented:
is sched_date a date or a string?



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

Author

Commented:
date in the format MM/DD/RRRR
Most Valuable Expert 2011
Top Expert 2012

Commented:
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.
Most Valuable Expert 2011
Top Expert 2012

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

that doesn't make sense

dates don't have formats.

strings have formats

Author

Commented:
the user enters 053012 and I format it to 05/30/2012
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
awking00Information Technology Specialist

Commented:
>>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
Most Valuable Expert 2011
Top Expert 2012

Commented:
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;

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial