Reputation: 49
i want to do the following in SQL :
I have three shifts:
Shift 1 : 07:00 - 14:59
Shift 2: 15:00 - 22:59
Shift 3: 23:00 - 06:59
I want to know which shift i am actually in, depending on my sysdate (time). The following statement doesn't work for "02:00". start_time and end_time are varchar2
SELECT * FROM abc
WHERE to_char(sysdate, 'HH24:MI')
between start_time and end_time;
How can i do that?
Upvotes: 4
Views: 1623
Reputation: 3689
select *
from abc
where mod(EXTRACT(HOUR FROM CAST(sysdate AS TIMESTAMP))+1,24)
between mod(EXTRACT(HOUR FROM CAST(to_date(start_time,'hh24:mi') AS TIMESTAMP))+1,24)
and mod(EXTRACT(HOUR FROM CAST(to_date(end_time,'hh24:mi') AS TIMESTAMP))+1,24)
;
Upvotes: 3