| dynamic list of dates [message #313118] |
Thu, 10 April 2008 15:29  |
|
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.
select
to_date ( '10 2007','MM YYYY' ) - 1 + level as DateRange
from
dual
where
( 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.
2008-07
2008-08
2008-09
...
2008-15
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  |
Michel Cadot
Messages: 15244
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
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.
Regards
Michel
|
|
|
| Re: dynamic list of dates [message #313230 is a reply to message #313118 ] |
Fri, 11 April 2008 02:55 |
pablolee
Messages: 936
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.)
|
|
|
]
Members
Search
Help
Register
Login
Home
