Query records that fall between two week numbers

Question

I'm looking to create a query in Oracle (Web Intelligence to be specific) that selects records based on the current week number. I'm using the ISO standard so the format is "IW". I understand this is an unusual request (to query based on week#. compared to returning as a week # format), however I'm working in Public Health and it is a common practice to query data based on the current epidemiological week. In this instance, I am substituting the epi week for the ISO standard.

In pseudo code it would look like this:

 SELECT * FROM * WHERE date BETWEEN *WEEK#ONE* AND *CURRENTWEEK#*

WEEK#ONE would be a digit between 1-52 that is 3 less than CURRENTWEEK#, which is the current ISO week number.

So far I have started dabbling in to_date('01', 'IW') as an example, but I just keep hitting road blocks. Any help would be really appreciated :)

Don't you also need to limit the results on year? The pseudo code suggests that all records matching the given week interval, regardless of the year, should be returned. — Daniel B, Jan 06 '14 at 21:18
`TRUNC(sysdate-21,'IW')` gets you the starting date of the week 3 weeks ago. You can build your query from that without needing any PL/SQL. — Marc, Jan 06 '14 at 22:36

MT0 · Answer 1 · 2014-01-06T22:51:38.947

You can create a function to convert a year and an ISO week number to a date:

SQL Fiddle

CREATE FUNCTION TO_ISO_WEEK_DATE(
  week NUMBER,
  year NUMBER
) RETURN DATE DETERMINISTIC
IS
BEGIN
  RETURN NEXT_DAY(
          TO_DATE( year || '0104', 'YYYYMMDD' )
            - INTERVAL '7' DAY, 'MONDAY'
         )
         + ( week - 1 ) * 7;
END TO_ISO_WEEK_DATE;
/

Query 1:

SELECT LEVEL AS week,
       TO_ISO_WEEK_DATE( LEVEL, 2013 ) AS "date",
       TO_CHAR( TO_ISO_WEEK_DATE( LEVEL, 2013 ), 'IW' ) AS "Check"
FROM   DUAL
CONNECT BY LEVEL <= 5

Results:

| WEEK |                            DATE | CHECK |
|------|---------------------------------|-------|
|    1 | December, 31 2012 00:00:00+0000 |    01 |
|    2 |  January, 07 2013 00:00:00+0000 |    02 |
|    3 |  January, 14 2013 00:00:00+0000 |    03 |
|    4 |  January, 21 2013 00:00:00+0000 |    04 |
|    5 |  January, 28 2013 00:00:00+0000 |    05 |

You can then use this function to determine which dates you are searching between:

SELECT *
FROM   tbl
WHERE  "date" BETWEEN TO_ISO_WEEK_DATE( :currentweek - 3, 2014 )
              AND     TO_ISO_WEEK_DATE( :currentweek, 2014 );

If you want to do it without the function then you can unwrap the code within the function:

SELECT *
FROM   tbl
WHERE  "date" BETWEEN NEXT_DAY( TO_DATE( :currentyear || '0104', 'YYYYMMDD' ) - INTERVAL '7' DAY, 'MONDAY' ) + ( :currentweek - 4 ) * 7
              AND     NEXT_DAY( TO_DATE( :currentyear || '0104', 'YYYYMMDD' ) - INTERVAL '7' DAY, 'MONDAY' ) + ( :currentweek - 1 ) * 7;

Hi MTO, your suggestion is really well done. I'm quite impressed at this function. I think this can be used for another aspect of the problem not written here. However, because I'm in web intelligence at the moment I'm not able to use functions. I just want to say I really appreciate the work you put in. Thanks! — kschembri, Jan 06 '14 at 23:03
There is a small example at the end of how to use the code within the function without wrapping it in a function - its not as readable but will have the same functionality. — MT0, Jan 06 '14 at 23:08

score 1 · Accepted Answer · edited Jan 06 '14 at 22:22

1

Is this what you are looking for?

WHERE to_char(date, 'YYYY-IW') BETWEEN to_char(sysdate - 21, 'YYYY-IW') AND
                                       to_char(sysdate, 'YYYY-IW')

edited Jan 06 '14 at 22:22

Mark J. Bobak

13,720
6
39
67

answered Jan 06 '14 at 21:27

Gordon Linoff

1,242,037
58
646
786

Thank you! This worked well with web intelligence. My issue is that I didn't change the format of the date after the initial WHERE statement and my math was after the to_char, not within it. Thanks! – kschembri Jan 06 '14 at 23:04

Query records that fall between two week numbers

2 Answers2