SQL statement to get the weeks before the current date

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, 
  || ' - '
  || 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s