Avatar of anumoses
anumoses
Flag for United States of America asked on

oracle date question

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?
Oracle Database

Avatar of undefined
Last Comment
anumoses

8/22/2022 - Mon
slightwv (䄆 Netminder)

Not sure I follow but maybe this?

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

ASKER
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.
Sean Stuber

select to_date('30-may-2012','dd-month-yyyy')  + 6 from dual
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sean Stuber

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?
anumoses

ASKER
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
Sean Stuber

is sched_date a date or a string?



if it's a date, don't use to_date on it
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
anumoses

ASKER
date in the format MM/DD/RRRR
Sean Stuber

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.
Sean Stuber

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

that doesn't make sense

dates don't have formats.

strings have formats
Your help has saved me hundreds of hours of internet surfing.
fblack61
anumoses

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

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
awking00

>>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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Sean Stuber

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;
anumoses

ASKER
thanks