Skip to Main Content

query for all hours in a month?

764672Apr 5 2010 — edited Apr 5 2010

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:

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
Post Details
Added on Apr 5 2010