Select 2017 as year, 40 as week from dual union all
Select 2017 as year, 39 as week from dual union all Now it is easy to find the start day in any given week in the year by simply adding 7 days for each week (not counting the first week). Subtracting 3 days will give me the first day (sunday) of the first week of the year. The 4th of JAN 2017 happens to be a wednesday (day 4). Oracle will tell me the day of week (sun=1, sat=7) for any date using to_char(date, 'D'). Therefore I can generate a valid date in the first week of any year by constructing the 4th of January like: to_date(year || '-01-04', 'yyyy-mm-dd'). The following query builds on the assumption (ISO 8601) that the 4th of January is present in the first week in a year. These kinds of problems are easy to solve with calendar tables. Obvious this solution uses my NLS Settings (English): you may need to tweak the solution if you use different settings. New 2: select to_date( 23 *7, 'DDD') as dt from dual Old 2: select to_date( &iw *7, 'DDD') as dt from dual Then you can use next_day() function to get the previous Monday and the next Sunday relative to that date: SQL> with tgt as (Ģ select to_date( &iw *7, 'DDD') as dt from dualĤ select next_day(dt-8, 'mon') as start_date You need to multiple the IW number by 7 which you can convert to a date with the day of year mask.
However, it's easy enough to generate a range for the the IW week number. You're confusing 'IW' (which runs MON - SUN) with 'WW' which runs from the first day of the year: SQL> with dts as (
0 kommentar(er)
