4 connect by level <= 366 5 ), By signing up you agree to our Terms of Use and Privacy Policy. 10 select min(dt)
3 from dual 14-FEB-09 7 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 The modulo (MOD) expression returns 0 if today is Saturday, 1 if it is Sunday, etc.
Newsletters may contain advertising. 10 select min(dt) Is there a quick way to convert week number (of the year) to start date of that week? 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. In such a system February 8, 2009, would be the starting date for Week 7 (because Week 1 only has three days, January 1,2 and 3, a strange occurrence indeed as it contradicts the stated definition of every week starting on a Sunday). For getting monday ‘s date — select trunc(nbt_date,’iw’) from dual, For getting saturday’s date — select trunc(nbt_date,’iw’) + 5 from dual.
6 wk_dt as ( 09-FEB-09 2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 1 dt 13-FEB-09 7 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. 4 connect by level <= 366 14-FEB-09 7 10 select dt, wk_of_yr 12 where wk_of_yr = &&1 Defaults to the current date. It will not list all week’s first date and last date falling within thegiven period.
DT WK_OF_YR Example. SQL> with date_wk as ( 7 select dt, to_number(to_char(dt, 'ww')) wk_of_yr If you can control the NLS territory setting (or even better: you want to adapt to it), then the simplest solution is to use TRUNC(SYSDATE,'D') and TRUNC(SYSDATE,'D')+6 : we have also used the next_day for doing windowing outside of NLS setting. 3 from dual 12 where wk_of_yr = &&1 Is this answer out of date? I like to use TRUNC function on dates. --------- 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. 4 connect by level <= 366
SQL>, Put Your Oracle Database In the Oracle Cloud, Pseudonymization Of Personal Data With Oracle.
13 / 7 select dt, to_number(to_char(dt, 'iw')) wk_of_yr
SQL>
The DayName function extracts the name of the day of the week from a given date. 5 ), --------- ---------- 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. 13 /
4 connect by level <= 366 11 from wk_dt 29-DEC-08 1
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. 10-JAN-10 1 30-DEC-08 1
9 ) 07-JAN-10 1 Join a community of over 1M of your peers.