blogs.conchango.com

welcome to the conchango blogging site
Welcome to blogs.conchango.com Sign in | Join | Help
in Search
Blogs

Jim 2.0

Oracle SQL - Generate a list of dates

I have over the last couple of days been working with Jamie Thomson on a sql query to return a daily value inferred from periodic data readings throughout a month. One of the requirements to creating this query was to generate a list of days in the month. Sounds pretty easy except that we couldn't use procedural logic (such as a cursor), so it actually took a little bit of time to figure it out hence I thought it was worthwhile sharing. My thanks go to 'Hampus' for their blog where I found out how to do this.

The database we are querying is in Oracle, so the following sql has been written in TOAD, though I notice Jamie has already posted a corresponding version for SQL Server and he has bullied/cajouled me into posting the Oracle version, which incidentally is much simpler.

    1 select to_date('&date','MM YYYY')-1 + level as DateRange

    2 from    dual

    3 where   (to_date('&date','MM YYYY')-1+level) <= last_day(to_date('&Date','MM YYYY'))

    4 connect by level<=31

    5 ;

Very straightforward once you know how; the range of dates is controled by both the where clause and the connect by (number of levels). In the example above it will only return values for a month up to a maximum of 31 days. You could limit to return only the first half of a month by reducing the connect by level to 15, alternatively you can increase the range to any date range, such as a year by changing the to_date in the where clause. As you can see I'm passing the date variable into the query and using Oracle's date functions to determine the range for me because I feel this is a more user friendly experience for anyone else that has to run my scripts, but you could simply hard code the dates in.

Hope this helps,

James

Published 15 January 2007 16:43 by James.Pipe

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jim 2.0 said:

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

April 24, 2007 16:17
 

inahurrydba said:

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

September 27, 2007 23:13
 

Natesh said:

Great work.. Thanks for knowledge shared

November 6, 2007 08:53
 

Demba said:

Hi

When i execute this request on oracle database

it returns only 25 days

why this???

November 14, 2007 16:06
 

James.Pipe said:

The number of days returned depends on three variables; the start date entered as '&date', the end date in the where clause, which in this case is set to the last day of the month entered, and the maximum number of levels in the 'conect by' clause.

Check what values you have these set too, in particular you may want to change the format of the date variable you have to enter to suite your needs. I have it specified above so that it requires only a 'MM YYYY' and it finds the start of the month from this, so the range returned should be a maximum of 31 days from the start of month to the end of month. If you're not getting this then you may have inadvertantly altered one of these.

November 14, 2007 16:26
 

Rich said:

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

November 26, 2007 19:01
 

Rich said:

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

November 26, 2007 19:09
 

Bill said:

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.

December 10, 2007 15:56
 

paul said:

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.

December 28, 2007 20:06
 

James.Pipe said:

I think you have 2 problems; the first being that you need the expression to be <your date>-1 + (level*7), since the first level is 1 so you need to account for this otherwise you'll be a day ahead all the way through.

Also, you're multiplying the level by 7, but in the connect by clause you're specifying to end when level exceeds 2, therefore as soon as you start you've already exceeded this as you start with 7 (1*7). I'm assuming you want to stop after 2 weeks, so probably need to enter 14 in your connect by claus (or 2*7) if you prefer.

I must confess I've not tried to use this statement in this way, so my best advice would be to try what I've suggested here and see if that works, otherwise post another comment and we'll see if we can figure it out.

Cheers,

James

December 29, 2007 14:09
 

paul said:

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

December 31, 2007 16:17
 

paul said:

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

December 31, 2007 16:18
 

Paul said:

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

January 4, 2008 13:37
 

James.Pipe said:

i think the reason it works fine if you remove the connect by clause is because then it is just looking for week_ending_date = June 17th.

Apologies for the slow response, but I was on holiday so wasn't able to play around with the actual sql, hence had difficulty understanding exactly what you were doing. I think I can see the problem however; you're returning a range of dates using the connect by expression, yet you're looking for a week_ending_date '=' something, so this will never resolve.

What I think you need to do is put the date range expression in a sub query as you did before e.g.

(

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

from dual

connect by level <= 6

)

and use where week_ending_date IN <result from the sub query>

i.e.

SELECT employee_id, reg_hours, week_ending_date

FROM employee_hours_by_week

WHERE week_ending_date in (

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

from dual

connect by level <= 6

)

Does that make sense? Hopefully that will work.

Thanks,

James

January 7, 2008 19:56
 

Sam Prince said:

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.)

April 11, 2008 12:51
 

LTSpeed said:

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'.

June 10, 2008 12:43
 

Jill said:

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

June 17, 2008 06:13

Leave a Comment

(required) 
(optional)
(required) 
Submit

This Blog

Syndication

News

Locations of visitors to this page

Charity Checkout

My Blog Juice
© 2008 - Any comments or statements made herein do not necessarily reflect those of Conchango.
Powered by Community Server (Personal Edition), by Telligent Systems