For a report requirement I needed to select all hours between 2 dates.
The report contains a few parameters:

  • ” AS FROM_DATE,
  • ” AS TO_DATE,
  • ” AS FROM_TIME,
  • ” AS TO_TIME

The base of this code i found at http://gennick.com/database/dates-in-a-range
When all are blank the code automatically chooses the dates and time :

FROM_DATE = Current_date-1
TO_DATE = Current_date
FROM_TIME = 06:00:00
TO_TIME = 05:59:59

WITH PARAM AS
(SELECT
TO_CHAR(TO_DATE(CASE WHEN FROM_DATE IS NULL THEN TO_CHAR(CURRENT_DATE-1, 'DD-MON-YYYY' ) ELSE FROM_DATE END, 'DD-MON-YYYY'),'DD-MON-YYYY') AS FROM_DATE,
TO_CHAR(TO_DATE(CASE WHEN TO_DATE IS NULL THEN TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY' ) ELSE TO_DATE END, 'DD-MON-YYYY'),'DD-MON-YYYY') AS TO_DATE,
TO_CHAR(TO_TIMESTAMP(NVL(from_time, '06:00:00'),'HH24:MI:SS'),'HH24:MI:SS') AS FROM_TIME,
TO_CHAR(TO_TIMESTAMP(NVL(to_time, '05:59:59'),'HH24:MI:SS'),'HH24:MI:SS') AS TO_TIME
FROM
(SELECT
'' AS FROM_DATE, -- DD-MON-YYYY
'' AS TO_DATE, -- DD-MON-YYYY
'' AS FROM_TIME,
'' AS TO_TIME
FROM DUAL
)
)
Select * from param;
FROM_DATE   TO_DATE     FROM_TIME TO_TIME
14-APR-2017 14-APR-2017 06:00:00 05:59:59

The code a came up with is written below.


WITH PARAM AS
(SELECT
 TO_CHAR(TO_DATE(CASE WHEN FROM_DATE IS NULL THEN TO_CHAR(CURRENT_DATE-1, 'DD-MON-YYYY' ) ELSE FROM_DATE END, 'DD-MON-YYYY'),'DD-MON-YYYY') AS FROM_DATE,
 TO_CHAR(TO_DATE(CASE WHEN TO_DATE IS NULL THEN TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY' ) ELSE TO_DATE END, 'DD-MON-YYYY'),'DD-MON-YYYY') AS TO_DATE,
 TO_CHAR(TO_TIMESTAMP(NVL(from_time, '06:00:00'),'HH24:MI:SS'),'HH24:MI:SS') AS FROM_TIME,
 TO_CHAR(TO_TIMESTAMP(NVL(to_time, '05:59:59'),'HH24:MI:SS'),'HH24:MI:SS') AS TO_TIME
FROM
 (SELECT
 '' AS FROM_DATE,
 '' AS TO_DATE,
 '' AS FROM_TIME,
 '' AS TO_TIME
 FROM DUAL
 )
)
,
PARAMS AS
(Select
 DATES.FROM_DATE,
 DATES.TO_DATE,
 DATES.FROM_TIME,
 DATES.PERIOD,
 DATES.TO_TIME,
 DATES.HOURS,
 DATES.HH24,
 DATES.DT
FROM
(SELECT
 PARAM.FROM_DATE,
 PARAM.TO_DATE,
 PARAM.FROM_TIME,
 PARAM.TO_TIME,
 CASE WHEN TO_CHAR(TO_DATE(PARAM."FROM_DATE" || ' ' || NVL(PARAM.from_time, '06:00:00'), 'DD-MON-YYYY hh24:mi:ss')+ ( LEVEL - 1 ) / 24 ,'HH24') between 06 and 13 THEN 'AM'
 WHEN TO_CHAR(TO_DATE(PARAM."FROM_DATE" || ' ' || NVL(PARAM.from_time, '06:00:00'), 'DD-MON-YYYY hh24:mi:ss')+ ( LEVEL - 1 ) / 24 ,'HH24') between 14 and 21 THEN 'PM'
 ELSE 'NIGHT'
 END AS PERIOD,
 TO_CHAR(TO_DATE(PARAM."FROM_DATE" || ' ' || NVL(PARAM.from_time, '06:00:00'), 'DD-MON-YYYY hh24:mi:ss')+ ( LEVEL - 1 ) / 24,'DD-MON-YYYY HH24:MI:SS') HOURS,
 TO_CHAR(TO_DATE(PARAM."FROM_DATE" || ' ' || NVL(PARAM.from_time, '06:00:00'), 'DD-MON-YYYY hh24:mi:ss')+ ( LEVEL - 1 ) / 24 ,'HH24') AS HH24,
 TO_CHAR(TO_DATE(PARAM."FROM_DATE" || ' ' || NVL(PARAM.from_time, '06:00:00'), 'DD-MON-YYYY hh24:mi:ss')+ ( LEVEL - 1 ) / 24,'DD-MON-YYYY') AS DT
 FROM PARAM
 CONNECT BY ( LEVEL - 1 ) <=((TO_DATE(PARAM."TO_DATE"|| ' '|| NVL(PARAM.to_time, '05:59:59'), 'DD-MON-YYYY hh24:mi:ss') - 1 / 86400 - TO_DATE(PARAM."FROM_DATE" || ' ' || NVL(PARAM.FROM_TIME, '06:00:00'), 'DD-MON-YYYY hh24:mi:ss')) +1 )* 24

) DATES
WHERE
 TO_TIMESTAMP(DATES.HOURS, 'DD-MON-YYYY hh24:mi:ss') BETWEEN
 TO_TIMESTAMP(DATES."FROM_DATE" || ' ' || DATES."FROM_TIME", 'DD-MON-YYYY hh24:mi:ss')
 AND To_Timestamp(DATES."TO_DATE"|| ' '|| DATES."TO_TIME", 'DD-MON-YYYY hh24:mi:ss')
)

Select * from params;

FROM_DATE TO_DATE FROM_TIME PERIOD TO_TIME HOURS HH24 DT
13-APR-2017 14-APR-2017 06:00:00 AM 05:59:59 13-APR-2017 06:00:00 06 13-APR-2017
13-APR-2017 14-APR-2017 06:00:00 AM 05:59:59 13-APR-2017 07:00:00 07 13-APR-2017
13-APR-2017 14-APR-2017 06:00:00 AM 05:59:59 13-APR-2017 08:00:00 08 13-APR-2017
13-APR-2017 14-APR-2017 06:00:00 AM 05:59:59 13-APR-2017 09:00:00 09 13-APR-2017
13-APR-2017 14-APR-2017 06:00:00 AM 05:59:59 13-APR-2017 10:00:00 10 13-APR-2017
13-APR-2017 14-APR-2017 06:00:00 AM 05:59:59 13-APR-2017 11:00:00 11 13-APR-2017
13-APR-2017 14-APR-2017 06:00:00 AM 05:59:59 13-APR-2017 12:00:00 12 13-APR-2017
13-APR-2017 14-APR-2017 06:00:00 AM 05:59:59 13-APR-2017 13:00:00 13 13-APR-2017
13-APR-2017 14-APR-2017 06:00:00 PM 05:59:59 13-APR-2017 14:00:00 14 13-APR-2017
13-APR-2017 14-APR-2017 06:00:00 PM 05:59:59 13-APR-2017 15:00:00 15 13-APR-2017
13-APR-2017 14-APR-2017 06:00:00 PM 05:59:59 13-APR-2017 16:00:00 16 13-APR-2017
13-APR-2017 14-APR-2017 06:00:00 PM 05:59:59 13-APR-2017 17:00:00 17 13-APR-2017
13-APR-2017 14-APR-2017 06:00:00 PM 05:59:59 13-APR-2017 18:00:00 18 13-APR-2017
13-APR-2017 14-APR-2017 06:00:00 PM 05:59:59 13-APR-2017 19:00:00 19 13-APR-2017
13-APR-2017 14-APR-2017 06:00:00 PM 05:59:59 13-APR-2017 20:00:00 20 13-APR-2017
13-APR-2017 14-APR-2017 06:00:00 PM 05:59:59 13-APR-2017 21:00:00 21 13-APR-2017
13-APR-2017 14-APR-2017 06:00:00 NIGHT 05:59:59 13-APR-2017 22:00:00 22 13-APR-2017
13-APR-2017 14-APR-2017 06:00:00 NIGHT 05:59:59 13-APR-2017 23:00:00 23 13-APR-2017
13-APR-2017 14-APR-2017 06:00:00 NIGHT 05:59:59 14-APR-2017 00:00:00 00 14-APR-2017
13-APR-2017 14-APR-2017 06:00:00 NIGHT 05:59:59 14-APR-2017 01:00:00 01 14-APR-2017
13-APR-2017 14-APR-2017 06:00:00 NIGHT 05:59:59 14-APR-2017 02:00:00 02 14-APR-2017
13-APR-2017 14-APR-2017 06:00:00 NIGHT 05:59:59 14-APR-2017 03:00:00 03 14-APR-2017
13-APR-2017 14-APR-2017 06:00:00 NIGHT 05:59:59 14-APR-2017 04:00:00 04 14-APR-2017
13-APR-2017 14-APR-2017 06:00:00 NIGHT 05:59:59 14-APR-2017 05:00:00 05 14-APR-2017

This gives the me all the full hours (HH)  between the 2 dates and times which forms the bases of the further report.

Feel free to copy and adjust the code. I found it helpfull.

Advertisements