Home » SQL & PL/SQL » SQL & PL/SQL » dynamic list of dates (10g)
dynamic list of dates [message #313118] Thu, 10 April 2008 15:29 Go to next message
Messages: 4
Registered: May 2006
Location: Bellevue, WA
Junior Member

I would like to return a list of ISO weeks between a range:
SELECT MIN (dt) AS mi, MAX (dt) AS ma FROM tbl;

Currently this returns 17-Jan-2008 and 10-Apr-2008

Using this example as a starting point. It will dynamically create a list of dates for the month of Oct 2007.
    to_date ( '10 2007','MM YYYY' ) - 1 + level as DateRange
    ( to_date ( '10 2007','MM YYYY' ) - 1 + level ) <= last_day ( to_date ( '10 2007','MM YYYY' ) )
  connect by 
    level <= 31

But I can't seem to get this to work. What I'd like to do is eventually return a list of ISO weeks
TO_CHAR ( dt, 'IYYY-IW') AS iso_week

between the above dates. E.g.


I tried putting my min/max query in place of DUAL but it would only return one row. I could not get it to continue on to the max date. I am guessing it could not continue like it could with the dual table. My min/max only returned one row, so it only gave me one row. How can I get the connect by to start with my min date and continue on to my max date?

This will be part of a report that will run weekly, so the end date will be changing.

Any ideas?

Re: dynamic list of dates [message #313121 is a reply to message #313118] Thu, 10 April 2008 15:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63920
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT an expert question.

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Read them, follow them including what is said about formatting.

As you are an expert, I don't answer the question because you obviously already knows it.

Re: dynamic list of dates [message #313230 is a reply to message #313118] Fri, 11 April 2008 02:55 Go to previous message
Messages: 2818
Registered: May 2007
Location: Scotland
Senior Member
As Michel says, this is not an expert level question. You have (basically) date + LEVEL
Which will add one day on to the previous row's date. What you actually want to do is add 1 WEEK onto the previous row's date. (simple alteration of your arithmetic in the date generation.)
Previous Topic: filtering columns with NULL values
Next Topic: need help on writing query for a complex situation
Goto Forum:

Current Time: Tue Oct 25 04:29:18 CDT 2016

Total time taken to generate the page: 0.11898 seconds