The 4th of JAN 2017 happens to be a wednesday (day 4). 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. Can anyone tell me how to get Start Date and End Date in a week (Mon-Sat) if any date in between is given as input. 13 / 05-JAN-10 1 29-DEC-08 1 8 from date_wk 9 ) 11 from wk_dt 12 where wk_of_yr = &&1 11 from wk_dt 5 ), 18-FEB-09 7 13 / Oracle will tell me the day of week (sun=1, sat=7) for any date using to_char (date, 'D'). 10-FEB-09 7 I don't know of a numbering scheme which meets that conflicting criteria. SQL> with date_wk as ( Function . 10 select dt, wk_of_yr 07-JAN-10 1 3 from dual 9 ) Stranger things have happened. The change to the format specifier is the only change made to the query posted at the beginning.]. 7 rows selected. 13 / 2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 1 dt Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. 14-FEB-09 7 12 where wk_of_yr = &&1 The above query uses the Julian date (2415026) of the first Saturday in the 20th century (January 6th, 1900) as an anchor point. SQL> 9 ) 2 select to_date('01/01/2010', 'MM/DD/RRRR') + rownum - 1 dt 06-FEB-09 6 SQL> If I pass week number as 7 then it should return 2/8/2009, SQL> with date_wk as ( We encourage you to read our updated PRIVACY POLICY and COOKIE POLICY. MIN(DT) The first subquery shown generates a list of dates starting with January 1 of the current year and ends 365 days later. 13 / The second subquery takes that list and generates the U.S. week number for each date. 4 connect by level <= 366 13 / It helped me very much. 9 ) DT WK_OF_YR Defaults to the current date. 9 ) 7 select dt, to_number(to_char(dt, 'iw')) wk_of_yr --------- iw is nothing but international week starts on Monday. Thanks. 4 connect by level <= 366 The Oracle documentation contains a complete SQL reference. 8 from date_wk 12 where wk_of_yr = &&1 6 wk_dt as ( 10 select dt, wk_of_yr By signing up you agree to our Terms of Use and Privacy Policy. 08-JAN-10 1 2 select to_date('01/01/2010', 'MM/DD/RRRR') + rownum - 1 dt DT WK_OF_YR 10 select dt, wk_of_yr The two week numbering systems in use by Oracle are the U.S. week numbering system and the ISO week numbering system. 13 / 4 connect by level <= 366 This allows the first week of the year to start on any day of the conventional week and end six days later; the first week could run from Wednesday to Tuesday rather than from Sunday to Saturday. SQL> Select TRUNC(sysdate, ‘IW’) FROM_DATE, 2 NEXT_DAY(TRUNC(sysdate+50,’IW’),’SUNDAY’) TO_DATE from dual; FROM_DATE TO_DATE——— ———23-JUN-03 17-AUG-03. 8 from date_wk --------- ---------- Question: I want to display the start date and end date for a specific week. 2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 1 dt 4 connect by level <= 366 ), always end on Saturday and declare Week Number 1 as that week starting with January 1 (a criteria that can run afoul of the Sunday to Saturday, 7 days in a week 'rule'). 3 from dual --------- ---------- 06-JAN-10 1 15-FEB-09 7 3 from dual 14-FEB-09 7 12 where wk_of_yr = &&1 Definition, Best Practices, and Tools. To get the corresponding four-digit year, use SELECT TO_CHAR (datecol, 'IYYY') FROM …. 5 ), 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’) Try This….this is in Sql server 2000……..————-, declare @d datetimedeclare @numbers table (n int), insert into @numbers(n)select 0 union allselect 1 union allselect 2 union allselect 3 union allselect 4 union allselect 5 union allselect 6 union allselect -1 union allselect -2 union allselect -3 union allselect -4 union allselect -5 union allselect -6, select min(d) AS WeekBegin, max(d) AS WeekEndfrom( select dateadd(d, n, @d) as d, datepart(week, dateadd(d, n, @d)) as w from @numbers) twhere datepart(week, @d) = w. SELECT DECODE (trim(TO_CHAR (in_date), ‘day’)), ‘monday’, in_date)+0, ‘tuesday’, in_date) – 1, ‘wednesday’, in_date) – 2, ‘thursday’, in_date) – 3, ‘friday’, in_date) – 4, ‘saturday’,in_date)-5, ‘sunday’,in_date)-6 ) start_day, DECODE (trim(TO_CHAR (in_date), ‘day’)), ‘monday’, in_date)+5, ‘tuesday’, in_date) + 4, ‘wednesday’, in_date) + 3, ‘thursday’, in_date) + 2, ‘friday’, in_date) + 1, ‘saturday’,in_date)+0, ‘sunday’,in_date)+6 ) end_day FROM DUAL. If 1 January is on a Friday, Saturday or Sunday, it is in week 52 or 53 of the previous year. 5 ), Connor and Chris don't just spend all day on AskTOM. 7 select dt, to_number(to_char(dt, 'iw')) wk_of_yr 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? SQL> 11 from wk_dt 7 select dt, to_number(to_char(dt, 'ww')) wk_of_yr 11 from wk_dt DT WK_OF_YR 07-FEB-09 6, SQL> with date_wk as ( 7 select dt, to_number(to_char(dt, 'iw')) wk_of_yr Asked: April 06, 2017 - 7:31 pm UTC. Example. If we use the U.S week numbering system we can easily satisfy the first condition listed in the posted question as Week 2 is defined as the week containing January 1: [In Oracle syntax the format specifier for the U.S. week numbering system is WW, in either upper or lower case. Week number 1 in this convention is defined as the week beginning on January 1, which may be a partial week based on the convention that calendar weeks start on Sunday and end on Saturday. 8 from date_wk Datestring = DateAdd( , , 1); WeekDay(datestring) 5. 3 from dual 2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 4 dt And of course, keep up to date with AskTOM via the official twitter account. 6 wk_dt as ( 17-FEB-09 7 11 from wk_dt 13 / (You could use any Saturday before your oldest "today" as the anchor.) 5 ), 10 select dt, wk_of_yr SQL>, SQL> with date_wk as ( Subtracting 3 days will give me the first day (sunday) of the first week of the year. Join a community of over 1M of your peers. 5 ), Here are some examples: (Assume the current date is Wednesday, July 5, 2009.) 10 select min(dt) As such the last week of the year in this convention can also be a partial week based on the convention stated in the previous sentence. The DayName function extracts the name of the day of the week from a given date. SQL>, Put Your Oracle Database In the Oracle Cloud, Pseudonymization Of Personal Data With Oracle. 11 from wk_dt 2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 4 dt 5 ), MIN(DT) 3 from dual 30-DEC-08 1 3 from dual Let's look at both of them and see what differences they contain and how they can throw the listed assumptions 'out of the window'. 5 ), --------- ---------- 05-FEB-09 6 WeekDay ( ) 4. You can also catch regular content via Connor's blog and Chris's blog. 9 ) First Date of Week(Monday Date) Using PO_DT field in Oracle, Trying to get the First Date of week based on value of PO_DT.