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

Advertisements


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s