Re: Trick query to return week ending date

From: Steve Cosner <stevec_at_zimmer.csufresno.edu>
Date: 18 Apr 1999 05:40:56 GMT
Message-ID: <7fbr98$bte$1@nntp.csufresno.edu>

In article <19990416195353.09333.00001693_at_ng12.aol.com>, PGunda <pgunda_at_aol.com> wrote:
> I have a situation where I need to populate a list item with week
> ending date ( sunday) for the past 6 months so that the user can
> pick a value.
>
> By the way I want to implement it in WEB development using PL/SQL
> catridge. If any one has a solution for this, please reply.

Here are two queries that will produce what you need. One produces dates in ascending order, and the other descending. You need to substitute a table with at least 26 rows where I used "Any_Table".

The following is set up for use in SQL Plus, but the Selects should work anywhere.

Set pagesize 33
Column R format A3
Column Date Format A20

ttitle '-------------- Dates in descending order --------------' Select to_char(rownum) R,

       to_char(Sysdate+1-To_Number(To_Char(Sysdate,'D'))
               -7*(rownum-1),
               'fmDay, fmDD-Mon-YYYY') "Date"

  from Any_Table
  where rownum < 27;

ttitle '-------------- Dates in ascending order --------------' Select to_char(rownum) R,

       to_char(Sysdate+1-To_Number(To_Char(Sysdate,'D'))
               -7*(26-rownum),
              'fmDay, fmDD-Mon-YYYY') "Date"

  from Any_Table
  where rownum < 27; Received on Sun Apr 18 1999 - 00:40:56 CDT