• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 596
  • Last Modified:

Comparison time portion of date/time field

Hi,
I need to compare the time portion of a date time field so that I create identify which employee shift it falls into. I have the following SQL:

SELECT
lo.order_proc_id
,CASE
WHEN To_Date(to_char(lo.end_procedure_time,'hh24:mi')) >= '08:00' AND
To_Date(to_char(lo.end_procedure_time,'hh24:mi')) <= '16:30' THEN '8to4'
WHEN To_Date(to_char(lo.end_procedure_time,'hh24:mi')) > '16:30' AND
To_Date(to_char(lo.end_procedure_time,'hh24:mi')) <= '24:00' THEN '4-30toMidnight'
WHEN To_Date(to_char(lo.end_procedure_time,'hh24:mi')) > '24:00' AND
To_Date(to_char(lo.end_procedure_time,'hh24:mi')) <= '08:00' THEN 'MidnightTo8'
ELSE NULL
END AS shift_time
FROM lab_orders lo

I get an error message stating "Not a valid month". How do I fix this?
Many thanks in advance for your review!
0
wdelaney05
Asked:
wdelaney05
  • 3
2 Solutions
 
wdelaney05Author Commented:
Actually, if I removed the To_Date and left to_char on each statement, would that work?
0
 
wdelaney05Author Commented:
Or, would one not want to use to_char because applying comparison operands on strings may give incorrect results?
0
 
gatorvipCommented:
Try this:

case 
when ( lo.end_procedure_time >= trunc(lo.end_procedure_time, 'DD')
	 and lo.end_procedure_time < trunc(lo.end_procedure_time, 'DD') + 8/24) )then '8to4'
when ( lo.end_procedure_time >= trunc(lo.end_procedure_time, 'DD' + 8/24)
	 and lo.end_procedure_time <= trunc(lo.end_procedure_time, 'DD') + 16.5/24) then '8to4'
when ( lo.end_procedure_time > trunc(lo.end_procedure_time, 'DD' + 16.5/24)
	 and lo.end_procedure_time < trunc(lo.end_procedure_time, 'DD') + 1 ) then '4-30toMidnight'
end

Open in new window


It's unoptimized and you should probably check the bounds of the end points to make sure they are what you need.
0
 
slightwv (䄆 Netminder) Commented:
>>I get an error message stating "Not a valid month". How do I fix this?

The reason is to_date needs a format mask.

In addition to the one above, here is my attempt.

drop table tab1 purge;
create table tab1(col1 date);

insert into tab1 values(to_date('0800','HH24MI'));
insert into tab1 values(to_date('1500','HH24MI'));
insert into tab1 values(to_date('1630','HH24MI'));
insert into tab1 values(to_date('1631','HH24MI'));
insert into tab1 values(to_date('2359','HH24MI'));
insert into tab1 values(to_date('0000','HH24MI'));
insert into tab1 values(to_date('0759','HH24MI'));
commit;

select col1, case
	when  to_number(to_char(col1,'HH24MI')) between 8 and 1630 then '8to430'
	when  to_number(to_char(col1,'HH24MI')) between 1631 and 2400 then '430tomidnight'
	else 'midnightto8'
end
from tab1;

Open in new window

0
 
wdelaney05Author Commented:
I like both of these - just tried them and they both work.
Many thanks for your prompt replies!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now