CODEWITHSUNDEEP
sqloracle-database
Anja
Anja

Reputation: 49

Check if time is between two times

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

Answers (1)

sers
sers

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

Related Questions