List of datetime array using Oracle

The original problematic line :

 SELECT p_from_date + level - 1 dt FROM
       (
       SELECT to_date('1980-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss') AS p_from_date,
       to_date( '1980-11-03 00:00:00','yyyy-mm-dd hh24:mi:ss') AS p_to_date
       FROM dual
       )
     CONNECT BY level <= p_to_date - p_from_date

result :

DT
-------------------------
1980-11-01 00:00:00
1980-11-02 00:00:00

the fix :

 SELECT p_from_date + level/24 dt FROM
       (
       SELECT to_date('1980-11-01 01:00:00','yyyy-mm-dd hh24:mi:ss') AS p_from_date,
       to_date( '1980-11-03 09:00:00','yyyy-mm-dd hh24:mi:ss') AS p_to_date
       FROM dual
       )
     CONNECT BY level/24 <= p_to_date - p_from_date

result :

DT
-------------------------
1980-11-01 02:00:00
1980-11-01 03:00:00
1980-11-01 04:00:00
1980-11-01 05:00:00
1980-11-01 06:00:00
1980-11-01 07:00:00
1980-11-01 08:00:00
1980-11-01 09:00:00
1980-11-01 10:00:00
1980-11-01 11:00:00
1980-11-01 12:00:00
1980-11-01 13:00:00
1980-11-01 14:00:00
1980-11-01 15:00:00
1980-11-01 16:00:00
1980-11-01 17:00:00
1980-11-01 18:00:00
1980-11-01 19:00:00
1980-11-01 20:00:00
1980-11-01 21:00:00
1980-11-01 22:00:00
1980-11-01 23:00:00
1980-11-02 00:00:00
1980-11-02 01:00:00
1980-11-02 02:00:00
1980-11-02 03:00:00
1980-11-02 04:00:00
1980-11-02 05:00:00
1980-11-02 06:00:00
1980-11-02 07:00:00
1980-11-02 08:00:00
1980-11-02 09:00:00
1980-11-02 10:00:00
1980-11-02 11:00:00
1980-11-02 12:00:00
1980-11-02 13:00:00
1980-11-02 14:00:00
1980-11-02 15:00:00
1980-11-02 16:00:00
1980-11-02 17:00:00
1980-11-02 18:00:00
1980-11-02 19:00:00
1980-11-02 20:00:00
1980-11-02 21:00:00
1980-11-02 22:00:00
1980-11-02 23:00:00
1980-11-03 00:00:00
1980-11-03 01:00:00
1980-11-03 02:00:00
1980-11-03 03:00:00
1980-11-03 04:00:00
1980-11-03 05:00:00
1980-11-03 06:00:00
1980-11-03 07:00:00
1980-11-03 08:00:00
1980-11-03 09:00:00
 
 56 rows selected

p/s : now, am applying self-restraint not to jump onto our customer..

Get Free Email Updates!

Signup now and receive an email once I publish new content.

I will never give away, trade or sell your email address. You can unsubscribe at any time.

Like

Related Post

One Response

  1. dotsha says:

    Since “?” in bash wildcard matches exactly one character …

    rm -f ??????????

    would be faster to type … 😎

    Reply

Anything to add?

X