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