asked on

oracle date question- sunday to saturday - one week

select next_day(to_date('19-sep-2012','dd-mon-yyyy')-7, 'Sunday') day1,
next_day(to_date('19-sep-2012','dd-mon-yyyy'), 'Saturday') day2
from dual;

DAY1|DAY2
9/16/2012|9/22/2012
----------------------

select next_day(to_date('22-sep-2012','dd-mon-yyyy')-7, 'Sunday') day1,
next_day(to_date('22-sep-2012','dd-mon-yyyy'), 'Saturday') day2
from dual;

should get the same answer as above

DAY1|DAY2
9/16/2012|9/22/2012

But I get this

DAY1|DAY2
9/16/2012|9/29/2012

How do I fix my query? WHen the date is entered I need to get the week from sunday to saturday ( just one week ) for the given date.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

Our schedule is between sunday to saturday. So if they want to check hours scheduled it will be for the week. From the given date we have to get the sunday and saturday only for that week.

Sean Stuber

Another option

trunc( your_date ,'d') Sunday_date,
next_day(trunc( your_date ,'d'),'Saturday') Saturday_date

This is based on NLS settings that define Sunday to be start of week

anumoses

ASKER

Is there a way to get in my format? of given date?

anumoses

ASKER

My earlier code works fine until a saturday date is the given date. If the given date falls on a saturday then it goes 2 weeks. So any simple fix?

Sean Stuber

you're not formatting your results above, next_day returns a DATE type
as does TRUNC.

if you want the results formatted, put them in TO_CHAR

for example....

to_char(trunc( your_date ,'d') ,'yyyy-mm-dd')

Sean Stuber

the simple fix is to use http:#a38406241

for whatever date you want. No math (-7, -1, etc) is needed.

anumoses

ASKER

thanks