Welcome to EMC Consulting Blogs

In addition to the T-SQL calendar table , which I have posted seperately, I went the whole hog and figured

Outstanding! A simple thing, but not so easy. Thank-you for saving me so much effort. Regards.

Great work.. Thanks for knowledge shared

Hi

When i execute this request on oracle database

it returns only 25 days

why this???

When I execute the following It only returned 01-OCT-07.  Is this correct?  I was expceting every day of the month.

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

I figured it out.  Below works.  Great Job!!

SELECT DateRange

FROM

(

  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

)

ORDER BY DateRange

Fantastic stuff James.  I modded it a bit to use a subquery instead of the bind variable - it lets me grab a range of dates from another table dynamically.  Trick is keeping all the subqueries the same and putting the min/max range I'm looking for in the outermost SELECT.

I am trying to use this query and it is not working for me.  I have a table employee_hours_by_week that contains an employee_id, week_ending_date, and reg_hours which is a total of hours reported for each week in the year (52 records per employee).  when i run the query without the connect by, it works but as soon as I add the connect by, it never returns any values.  here is the query:

SELECT employee_id, week_ending_date, reg_hours

FROM

(

SELECT employee_id, reg_hours, week_ending_date

FROM employee_hours_by_week

WHERE

week_ending_date = to_date('24-Jun-07') - (level * 7)

and employee_id = '14874'

connect by level <= 2

)

Anyone have any ideas why this is not working?

thanks.

James,

Query is designed to go backwards through time one week at a time for each week ending date (where the week ends on sunday.)  Take this query for example:

SELECT date_range

FROM

(

SELECT to_date('24-jun-07') - (level * 7)date_range

from dual

connect by level <= 6

)

This will return the previous 6 week ending dates from Jun 17 back to May 13.  I do understand that it will skip Jun 24th.

I want this same logic to work with the table in my prior posting, but when I merge the query to return data from my table with the query to go backwards by weeks, even only going back 2 weeks (thus the >= 2), the query never returns an answer, it seems to go into never never land.

Thanks again.

Paul

I mean only two weeks - (level <= 2)

James,

I let the following query run until it returned results and the elapsed time was 1350 seconds:

SELECT employee_id, week_ending_date, reg_hours

FROM

(

SELECT employee_id, reg_hours, week_ending_date

FROM employee_hours_by_week

WHERE

week_ending_date = to_date('24-Jun-07') - (level * 7)

and employee_id = '14874'

connect by level <= 1

)

What is strange is that if I remove the 'connect by' clause, it runs instantaneous because there are only 52 rows in this table for this employee.  I am not sure what this crazy connect by is doing to me.

What I am looking for is a way to do recursion going backwards by week ending date.

Thanks.

Paul

If you're using Oracle I think you can use something similar to the approach used in this article:

http://www.orafaq.com/node/1884

Note the bit where he writes:

"

...

RIGHT OUTER JOIN

      (SELECT   Calendar_Dt

       FROM     TABLE (Tfn_date_range (p_dt_Start, p_dt_End))) c

ON (t.Sale_Date = c.Calendar_Dt)

The above query returns all detail data that needs to be analyzed. Note that it uses the table function tfn_date_rnge as one of its data sources, which returns a set of calendar dates within the specified range."

(He has a typo somehwere - it should be either "rnge" or "range" - but I don't have an Oracle instance to test against.)

This might work better for some people...

SELECT

       TO_CHAR(TO_DATE('04/01/2006', 'mm/dd/yyyy') + (LEVEL-1),'DD-MON-YYYY') datelist

FROM

       DUAL

CONNECT BY

       TO_NUMBER(TO_CHAR((TO_DATE('04/01/2006','mm/dd/yyyy') + (LEVEL-1)),'YYYYMMDD')) <= TO_NUMBER(TO_CHAR(SYSDATE+365,'YYYYMMDD'))

It will return all dates after 4/1/06 thru 'today+365'.

I was having problems with the to_date when I needed to pass in a date, so I found this to work:

SELECT     LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) + LEVEL daterange

     FROM DUAL

    WHERE LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) + LEVEL <= TRUNC(SYSDATE)

CONNECT BY LEVEL <= 31

Thanks very much for everyone who contributed to this. I was googling for a query like this and your work has really helped us.

Thanks once again.

Very useful SQLs. Great job guys.

Thanks.

You don't need where clause and end date. Start date and level that's what matters:

SELECT TO_DATE ('2008-01', 'yyyy-mm') - 1 + LEVEL AS daterange

FROM DUAL

CONNECT BY LEVEL <= 366

Excellent solved my problem

great piece of sql how could you modify it to take a start and end date?

Just what I was looking for - many thanks!

Not what I was looking for, but thanks anyways.

Thanks a lot for that query. It has save me a lot of time

This query works great if I need only one row per date.  However, my requirement was to get a date entry for every 5 minutes.   I ended up writing a custom function.  I have listed the function at http://meslie.blogspot.com/2009/10/oracle-generate-list-of-dates-with-time.html

Someone asked how to return all days in a date range. I used a formula to set level to the number of days you want returned.  End date - beg date + 1

select to_date('1-Oct-2007') - 1 + level as DateRange

  from dual

connect by level <= (to_date('15-Nov-2007') - to_date('01-Oct-07') + 1);

I needed to know the last day of the month in a range of dates so I did this.  It returns the last day of the month every level of the connect and takes the distinct values.  

select distinct last_day(to_date('1-Oct-2007') - 1 + level) as DateRange

  from dual

connect by level <= (to_date('1-Jan-2008') - to_date('01-Oct-07') + 1);

great !

I totally agree the standpoint of upstairs, and I believe this will be a trend. I often come this forum , rom here I learn much and know the newest tide! the content here  constantly update and I love it! Another I know some websites which often update their contents, you guys should browse if you are free.

Hi Jim,

Nice post. Keep up the good work.

Also, there exists a little modification to your code at http://aspdotnetcode.source-of-humor.com/CodeSnippets/Oracle/HowToGenerateDatesSequenceColumn.aspx which can be used to auto generate all dates present in the specified date range.

Thank you

Vasily's comment:

"You don't need where clause and end date. Start date and level that's what matters:

SELECT TO_DATE ('2008-01', 'yyyy-mm') - 1 + LEVEL AS daterange

FROM DUAL

CONNECT BY LEVEL <= 366

"

assumes that there are 366 days in a year.  This isn't always true and one would need to change the hardcoded value on a regular basis.

Also, I wanted it to return the days in the month for any arbitrary date (instead of giving it the first of the month).  Jill's attempt was more elegant than mine, though here's end's with the date in question instead of the end of the month.

So I ended up with this:

SELECT LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) + LEVEL daterange

 FROM DUAL

WHERE LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) + LEVEL <= last_day(TRUNC(SYSDATE))

CONNECT BY LEVEL <= 31

(note that the 31 in the connect by, can be any number >= the maximum days in any month, the actual termination occurs from the where clause)

Very nice !

Anyone can help me with this ?

Why the query takes forever to run if I use the column name for ' Connect by level < Duration ' ( Duration is the number column for how many days in my table )

If I put the constant like this 'Connect by level <10  and query returns instantaneous.

SELECT customer,current_date-level,qty

from factor

connect by level < duration

Thanks a lot James for sharing this. This is what I was looking for. I have modified it further to get hourly rows just be using "level/24".

Also, do you know any such shortcut way of handling Day Light Savings? Basically Handling long and short days...?

Thanks

Piyush

AWESOME!!! This was exactly what I was looking for!! Thanks a lot for sharing!!