0

I have to work with data retrieved and grouped on a weekly basis (ISO week) and my DB is structured on a daily basis (field: DATE). I need to write down a code which is rolling, so that given the current date, it calculate the week and the retrieve data in the previous 3 weeks, too.

So I write in the WHERE clauses:

TO_DATE(TO_CHAR(DATE, 'YYYYWW')) BETWEEN TO_DATE(TO_CHAR(TO_DATE(running_date, 'YYYYMMDD'), 'YYYYWW'), 'YYYYWW')-3 AND TO_DATE(TO_CHAR(TO_DATE(running_date, 'YYYYMMDD'), 'YYYYWW'), 'YYYYWW')

It doesn't seems to work though. Any suggestions on how to handle the problem?

Thanks a lot!

DaveZZ
  • 1

2 Answers2

0

You have to subtract 21 days when you want to recive the previous 3 weeks. If you subtract 3 then you recive only the last three days.

tonirush
  • 430
  • 1
  • 6
  • 14
0

You need to use 'IW', not 'WW' as the format mask for ISO week. From the Oracle docs:

IW = Week of year (1-52 or 1-53) based on the ISO standard.

WW = Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.

Community
  • 1
  • 1
cagcowboy
  • 30,012
  • 11
  • 69
  • 93