SQL statement to get the weeks before the current datePosted: 04/28/2012
I recently had to design a report to analyze the data of a week, so the user had to select that week using an input control. The data source of the combo box was the following SQL statement that runs in an Oracle Database:
select to_char(next_day(trunc(sysdate, 'DAY'), 'SATURDAY') - (level * 7) - 6, 'mm/dd/yyyy') || ' - ' || to_char(next_day(trunc(sysdate, 'DAY'), 'SATURDAY') - (level * 7), 'mm/dd/yyyy') week from dual connect by level <= 52 order by level
This code employs the hierarchical query clause CONNECT BY to select the initial and the final day of the weeks before the current date, a period of a year more or less.