asked on

Trying to pull records from a date field based on a time; if the date_scheduled falls between specific hours want to show the range

I'm trying to pull records from a date field based on a time; if the date_scheduled falls between specific hours want to show the range. I tried a case statement, but cant get it to work. Do I need to use different functions within the case statemen, or is my syntax bad. Below is how I tried to do it. Going against oracle db.

(case date_scheduled when (to_char(date_scheduled, 'hh24') >= 05 and to_char(date_scheduled, 'hh24') < 07) then '05:00-07:00'
when (to_char(date_scheduled, 'hh24') >= 07 and to_char(date_scheduled, 'hh24') < 09)then '07:00-09:00'
when (to_char(date_scheduled, 'hh24') >= 09 and to_char(date_scheduled, 'hh24') < 11)then '09:00-11:00'
when (to_char(date_scheduled, 'hh24') >= 11 and to_char(date_scheduled, 'hh24') < 13)then '11:00-13:00'
when (to_char(date_scheduled, 'hh24') >= 13 and to_char(date_scheduled, 'hh24') < 15)then '13:00-15:00'
when (to_char(date_scheduled, 'hh24') >= 15 and to_char(date_scheduled, 'hh24') <= 17)then '13:00-15:00'
when (to_char(date_scheduled, 'hh24') < 05) then 'Before Hours'
when (to_char(date_scheduled, 'hh24') > 17)then 'After Hours')as "Time Range"
end),

knightEknight

case date_scheduled when HOUR(date_scheduled) in (5,6) then '05:00-07:00'
when HOUR(date_scheduled) in (7,8) then '07:00-09:00'
when HOUR(date_scheduled) in (9,10) then '09:00-11:00'
when HOUR(date_scheduled) in (11,12) then '11:00-13:00'
when HOUR(date_scheduled) in (13,14) then '13:00-15:00'
when HOUR(date_scheduled) in (15,16) then '15:00-17:00'
when HOUR(date_scheduled) < 5 then 'Before Hours'
when HOUR(date_scheduled) > 17 then 'After Hours'
end) as 'Time Range',

knightEknight

oops, remove the ) after 'end' in the last line

van-coug

ASKER

Thanks knightEknight:

I tried it, but I'm getting an ORA-00905 missing keyword error- it highlighting the first 'in'.

select w.date_scheduled,
(case date_scheduled when HOUR(date_scheduled) in (5,6) then '05:00-07:00'
when HOUR(date_scheduled) in (7,8) then '07:00-09:00'
when HOUR(date_scheduled) in (9,10) then '09:00-11:00'
when HOUR(date_scheduled) in (11,12) then '11:00-13:00'
when HOUR(date_scheduled) in (13,14) then '13:00-15:00'
when HOUR(date_scheduled) in (15,16) then '15:00-17:00'
when HOUR(date_scheduled) < 5 then 'Before Hours'
when HOUR(date_scheduled) > 17 then 'After Hours'
end) as 'Time Range',
w.work_order_id
from oms.work_orders w
where w.work_order_id > trunc(sysdate)

knightEknight

Sorry, I thought this was SQL Server, not Oracle.

ASKER CERTIFIED SOLUTION