asked on

Oracle: Time range in query

Experts,

I need assistance with writing a query for the following criteria.

Any source of Construction created between 1am – 5:30am would be considered normal maintenance (non-outage).
Any source of Construction outside of those hours would be considered an outage.

I cant seem to add the time range properly. Any suggestions?

            (CASE
                WHEN UPPER(SOURCE) = 'CONSTRUCTION' 
                AND UDF_CONVERT_UNIX_DATETIME(DATE_CREATED, 'US/Eastern') 
                BETWEEN TO_DATE('01:00:00','HH24:MI:SS') and TO_DATE('05:30:00','HH24:MI:SS') THEN 'MNT'
                ELSE 'OUTAGE'
             END) AS FORM_TYPE,

Open in new window

SOLUTION

Gerwin Jansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Maliki Hassani

ASKER

ORA-01843: not a valid month

any other ideas?

Maliki Hassani

ASKER

Going to try something like this that I found
select *
from your_table
where creation_date > sysdate - 7
and to_char(creation_date, 'hh24:mi:ss') >= '06:45:00'
and to_char(creation_date, 'hh24:mi:ss') <= '19:15:00'

Maliki Hassani

ASKER

no such luck! still searching

ASKER CERTIFIED SOLUTION

flow01

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Maliki Hassani

ASKER

Thank you!