Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

query for all hours in a month?

764672Apr 5 2010 — edited Apr 5 2010
Hello,

I was hoping someone might be able to assist. I need to query for all the hours in a user specified date range (generally a month). For example,
a query such as:
select all hr_end from dual where start_date between '01-feb-2010' and '28-feb-2010'

that returns:

feb 01 2010 01:00
feb 01 2010 02:00
feb 01 2010 03:00
...
feb 28 2010 23:00
feb 28 2010 24:00

Ideally, I want to be able to run this query within a WITH clause such that I can reference the temporary table/ list of hours in a subsequent select statement quickly. Something like:

With
all_hrs_in_month AS
select .....
where start_date between '01-mar-2010' and '31-mar-2010'

I've looked around for similar questions, but have only found ones for all the 'days' in a month using level, connect by, row_num, for which I'm honestly not too familiar with and wasn't clear as to how I could modify to my needs.

Greatly appreciate your help with this request.

- j

Edited by: user12942939 on Apr 5, 2010 12:04 PM

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 3 2010
Added on Apr 5 2010
5 comments
4,972 views