Clever Geek Handbook

Check if the time is between the two

I want to do the following in SQL:

I have three shifts:

Offset 1: 07:00 - 14:59 Offset
2: 15:00 - 22:59
Offset 3: 23:00 - 6:59

I want to know what shift I am on, depending on my sysdate (time). The following statement does not 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 this?

+3


source to share


2 answers 


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)


;

+2


source


Consider the name of your varchar field at that time: charTime and the table name is abc. In mysql, you can do this:

select 
* 
from abc 
where DATE_FORMAT(charTime,'%H:%i') BETWEEN '2017-07-01'
AND '2017-10-01';

In Sqlsvr, you can do this:



SELECT
    CONVERT (
        VARCHAR (12),
        charTime,
        108
    )
FROM
    abc WHERE charTime BETWEEN '2017-07-01'
AND '2017-10-01'

In Oracle, you can do this (just use SQL):

select 
* 
from abc 
where charTime 
between to_date('2017-07-01','hh24:mi:ss') and to_date('2017-10-01','hh24:mi:ss')
-1


source






More articles:


All Articles