Selecting weeks using SQL

I have a drop-down named 'Select Week:' and this drop-down can have 3 weeks as its value i.e the current week, next week and the week after that. These values need to be updated into the drop-down using SQL statements.

Say for eg: Today is 03/12/2011, then in the drop-down, the options should be as follows: 03/06/2011 - 03/12/2011 (Sun to S开发者_如何学Pythonat) 03/13/2011 - 03/19/2011 (Sun to Sat) 03/20/2011 - 03/26/2011 (Sun to Sat)

I have written a small portion of the code using SELECT Case. Please help me with the code and the correct logic behind it. Thnx in advance for any help :)

 select case when
 to_char(sysdate,'day') = 'saturday' 
 then
 to_char(next_day(sysdate,'sunday')-7,'mm/dd/yyyy') || '-' || to_char
 (next_day(sysdate-1,'saturday'),'mm/dd/yyyy')
 else || '-' || to_char
 (next_day(sysdayte,'saturday'),'mm/dd/yyyy')
 end weeks from dual union .... union

....

It seems to me that you have a too tight dependency between SQL and GUI. You're putting too much pressure on one poor SQL query. Why don't you use SQL just to get data from the database, and leave all the calculation logic to a real programming language? It will make it much more readable and easier to maintain. This query looks so ugly, with all the string concatenations.

If this should be done in DB, then...

Just showing code for the weekno's. You could extract the start-end date for each time in a similar manner, but than I would probably store them in a local db-table and select them istead of a union .e.g: declare @weeks table(weekno int, starts datetime, ends datetime)

declare @now datetime; set @now = getdate();

declare @previousWeek int; set @previousWeek = datepart(week, (dateadd(week, -1, @now)));

declare @nextWeek int; set @nextWeek = datepart(week, (dateadd(week, 1, @now)));

declare @weekNow int; set @weekNow = datepart(week, @now);

select @previousWeek WeekNo union select @weekNow union select @nextWeek