Search code examples
sqloracle-databasewebobjects

Query records that fall between two week numbers

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 :)

Solution

  • 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')