10 select dt, wk_of_yr 09-FEB-09 7 --------- 12 where wk_of_yr = &&1 7 select dt, to_number(to_char(dt, 'iw')) wk_of_yr 7 select dt, to_number(to_char(dt, 'iw')) wk_of_yr 06-JAN-10 1 iw is nothing but international week starts on Monday. 5 ), 11 from wk_dt 4 connect by level <= 366 12 where wk_of_yr = &&1 7 select dt, to_number(to_char(dt, 'iw')) wk_of_yr 4 connect by level <= 366 SELECT sysdate today, TRUNC(sysdate, ‘D’) startofweek, TRUNC(sysdate, ‘D’) + 6 endofweekFROM DUAL; Hi If your question is to get start date & end date ( Monday & Saturday ) for every week which falls within the certain period given, then below is the query for the same..select TO_char(dt, ‘dd-mon-yyyy’) , TO_char(dt +5 ,’dd-mon-yyyy’) from (select :start_dt + level -1 dt from dual connect by level <= :end_dt+1 – :start_dt ) where TO_char(dt, ‘D’) = 2, here, :start_dt = ’10/01/2009′ means, 1st october :end_dt = ’12/31/2009′ means, 31st december…, here, :start_dt = ’10/01/2009′ means, 1st october :end_dt = ’12/31/2009′ means, 31st december…and the output will be…TO_CHAR(DT,’DD-MON-YYYY’) TO_CHAR(DT+5,’DD-MON-YYYY’)05-oct-2009 10-oct-200912-oct-2009 17-oct-200919-oct-2009 24-oct-200926-oct-2009 31-oct-200902-nov-2009 07-nov-200909-nov-2009 14-nov-200916-nov-2009 21-nov-200923-nov-2009 28-nov-200930-nov-2009 05-dec-200907-dec-2009 12-dec-200914-dec-2009 19-dec-200921-dec-2009 26-dec-200928-dec-2009 02-jan-2010, http://oracle.ittoolbox.com/groups/technical-functional/oracle-db-l/how-to-get-start-date-and-end-date-of-a-week-282653. 6 wk_dt as ( Select TRUNC(sysdate, ‘IW’) FROM_DATE, NEXT_DAY(TRUNC(sysdate,’IW’),’SUNDAY’) TO_DATEFrom dual; But this will give the First date and last date of the period given toNEXT_DAY function. --------- ---------- 07-FEB-09 6, SQL> with date_wk as ( The concept assumes a number of things which may or may not be true, such as the first day of week 1 is January 1 and that week 7 is defined to include, and also begin on, February 8. 12 where wk_of_yr = &&1 3 from dual 8 from date_wk If you use the ‘D’ format mask, it becomes much simpler…, D is day of the week 0=Sunday, 6=Saturday, SQL> list 1 select 2 to_char(sysdate,’Dy DD-Mon-YYYY’) today, 3 to_char(sysdate+ (1-to_char(sysdate,’D’)),’Dy DD-Mon-YYYY’) startofwk, 4 to_char(sysdate+ (7-to_char(sysdate,’D’)),’Dy DD-Mon-YYYY’) endofwk 5* from dualSQL> /, TODAY STARTOFWK ENDOFWK————— ————— —————Fri 26-Oct-2007 Sun 21-Oct-2007 Sat 27-Oct-2007. 3 from dual The above query uses the Julian date (2415026) of the first Saturday in the 20th century (January 6th, 1900) as an anchor point. 13-FEB-09 7 31-DEC-08 1, SQL> with date_wk as ( We'll send an email with a link to reset your password. 9 ) So, the question, as posed, relies upon a numbering system which allows partial weeks, the weeks always start on Sunday (so how does a partial week occur? Thanks. A long while back someone asked the following question, which seems simple enough: That same question popped up just recently so I dug out the example I made in2009 which is still relevant today. 10 select min(dt) Explanation. 12 where wk_of_yr = &&1 The first subquery shown generates a list of dates starting with January 1 of the current year and ends 365 days later.

.

Réchauffer Pizza Congelée Au Four, Top Management Ep 10, Buck Thomas Premadonna, Spanish Pronunciation Generator, Voot Colors Gujarati Rasoi Show, Angela Davis Wife, Scum Naval Base Map, Mary Berry Sultana Fairy Cakes, Paul Mellor Wife, Plastic Dashboard Repair Kit, Stonks Meme Variations, Dhool Song Lyrics,