-
WeekNumber to start of week date
Hi
Does anybody know of a way to convert a given weeknumber to date.
Say for example if i input weeknumber as 27 it should return the monday of that week and the return date should be 01/07/2008
regards
Hrishy
-
Originally Posted by hrishy
Say for example if i input weeknumber as 27 it should return the monday of that week and the return date should be 01/07/2008
Nobody can do that... problem is JUL-01-2008 wasn't a Monday, it was a Tueday
How about something like...
Code:
select next_day(trunc((to_date('01/01/2008', 'MM/DD/YYYY') + ( (27 - 1) * 7 ))), 'Monday')
from dual;
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Hi
Thanks for the reply.
But i am getting the right answer only when i do 27-2 i.e for week number 27 i should be doing 27-2 any idea why
Code:
select
next_day(trunc((to_date('01/01/2008', 'DD/MM/YYYY') + ( (27-2) * 7 ))), 'Monday')
from dual
NEXT_DAY(
---------
30-JUN-08
regards
Hrishy
-
I may have an idea... shouldn't we adjust our calendar? the first week of the year most of the time does not begins the first day of such a week.
Code:
select next_day(trunc( -- We want to get a next_day...
(to_date('01/01/2008', 'DD/MM/YYYY') + -- set first day of the year
( ((27-1) * 7) - -- set WEEK number e.g.: 27
(to_char(to_date('01/01/2008', 'DD/MM/YYYY'), 'D') - 1) ))), -- adjust first day of the year/week
'Monday') -- ...which is a Monday!
from dual
;
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Hi Pavb
Brilliant that works thank you very very much ofr the explanation
regards
Hrishy
-
"I love when a plan comes together!"
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by hrishy
Hi Pavb
Brilliant that works thank you very very much ofr the explanation
regards
Hrishy
the correct one is :
SELECT TRUNC(TO_DATE(2008||' '||((27-1)*7),'YYYY DDD'))
FROM dual;
27-> week number..
-
Hi mdvreddy
Absolutely Brilliant :-) and quite elegant solution too
regards
Hrishy
-
Originally Posted by mdvreddy
the correct one is :
SELECT TRUNC(TO_DATE(2008||' '||((27-1)*7),'YYYY DDD'))
FROM dual;
27-> week number..
Don't be so fast to discount PAVB's solution. His will actually work next year, whereas yours will NOT.
Code:
SQL> --- Your query next year
SQL> SELECT TRUNC(TO_DATE(2009||' '||((27-1)*7),'YYYY DDD'))
2 FROM dual;
TRUNC(TO_
---------
01-JUL-09 <--- This is a Wednesday
In addition to PAVB's "accurate" query, a territory independent option could be:
Code:
SQL> select trunc(trunc(sysdate, 'Y')+(27-1)*7, 'IW') from dual;
TRUNC(TRU
---------
30-JUN-08
SQL> --- Next Year
SQL> select trunc(trunc(sysdate+365, 'Y')+(27-1)*7, 'IW') from dual;
TRUNC(TRU
---------
29-JUN-09
-
Hi Ebrian
Thanks a million for showing me the importance of testing :-)
regards
Hrishy
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|