Home » SQL & PL/SQL » SQL & PL/SQL » Getting the start and End date of a week (Oracle 10g)
Getting the start and End date of a week [message #310251] Mon, 31 March 2008 13:22 Go to next message
sandy_vitty
Messages: 9
Registered: June 2005
Junior Member
Hi All

I have the week number of the year. The week starts from monday and ends on sunday. I have to get the week start date and week end date from the week number. Any help would be greatly appreciated.

Thanks
Re: Getting the start and End date of a week [message #310254 is a reply to message #310251] Mon, 31 March 2008 13:30 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
This has probably been answered before, but I'm looking for something to do.

I'd start with the fact that you can do this:

A@orcl > select trunc(sysdate,'YYYY') from dual;

TRUNC(SYSDATE,'YYYY'
--------------------
01-JAN-2008 00:00:00

1 row selected.


And the fact that a week has 7 days, meaning you can add days to the above, by multiplying by the number of weeks. Then it is a matter of knowing whether you start your week numbering system at 0 or 1.
Re: Getting the start and End date of a week [message #310257 is a reply to message #310251] Mon, 31 March 2008 13:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now you have to know if it is ISO week or local week.

Regards
Michel
Re: Getting the start and End date of a week [message #310263 is a reply to message #310257] Mon, 31 March 2008 13:52 Go to previous messageGo to next message
sandy_vitty
Messages: 9
Registered: June 2005
Junior Member
It is an ISO Week.
Re: Getting the start and End date of a week [message #310288 is a reply to message #310251] Mon, 31 March 2008 18:44 Go to previous messageGo to next message
oradev8
Messages: 8
Registered: April 2007
Junior Member
Try this :

select to_date(to_date(13*7,'DDD'),'dd-mon-yyyy'),
to_date(to_date(13*7,'DDD'),'dd-mon-yyyy') +6
from dual


13 is the week number...(which will be the i/p variable)
Re: Getting the start and End date of a week [message #310304 is a reply to message #310251] Mon, 31 March 2008 20:20 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
oradev8: did you try that? I doubt.

Back to the topic: this post on AskTom shows one possible way.

Alternatively you may take following approach:
- pick any day from given year, eg. April, 1st
- truncate it to ISO year
- add (week#-1)*7 days
Re: Getting the start and End date of a week [message #310621 is a reply to message #310304] Tue, 01 April 2008 15:54 Go to previous messageGo to next message
oradev8
Messages: 8
Registered: April 2007
Junior Member
The sql I posted gives the start and end date of a week for this year(2008) based on the week number...and I thik thats not what you want.

Thanks for pointing that out flyboy.

Re: Getting the start and End date of a week [message #310678 is a reply to message #310621] Wed, 02 April 2008 01:06 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Your query also does a to_date(to_date(....
You cannot do a to_date on a date, which means that the inner date is implicitly converted to a string first. Very bad idea.
Previous Topic: Can we override a function in PL/SQL environment
Next Topic: SOLUTION NEEDED FOR QUERY RELATED TO EM
Goto Forum:
  


Current Time: Thu Dec 08 10:14:00 CST 2016

Total time taken to generate the page: 0.09523 seconds