Update column based between two times Daily

Question

I would like to a SQL code to update the column area based on a interval of time daily.

also my start_time column is varchar2, not date column

eg:

between 06:00:00 and 10:00:00 = 'A'<br>
between 10:00:00 and 15:30:00 = 'B'<br>
between 15:30:00 and 22:00:00 = 'C'<br>

table name: myTable

   id    name        start_time        area
    ============================================
    1     a        06/07/19 11:00        -
    2     b        06/07/19 09:00        -
    3     c        06/07/19 11:00        -
    4     d        07/07/19 13:00        -
    5     e        07/07/19 21:00        - 
    6     f        08/07/19 16:00        -
    7     g        08/07/19 01:00        -
    8     h        08/07/19 18:00        -

Result:

   id    name       start_Time         area
  ============================================
    1     a        06/07/19 11:00        B
    2     b        06/07/19 09:00        A
    3     c        06/07/19 11:00        B
    4     d        07/07/19 13:00        B
    5     e        07/07/19 21:00        C 
    6     f        08/07/19 16:00        C
    7     g        08/07/19 01:00        -
    8     h        08/07/19 18:00        C

I did a SQL query that works:

select * 
from myTable 
where  TO_CHAR(TO_DATE(TIME_START,'YYYY-MM-DD HH24:MI:SS'), 'HH24:MI:SS') 
       BETWEEN TO_CHAR(TO_DATE('2019/11/11/ 06:00:00','YYYY-MM-DD HH24:MI:SS'), 'HH24:MI:SS') 
       AND TO_CHAR(TO_DATE('2019/11/11/ 13:30:00','YYYY-MM-DD HH24:MI:SS'), 'HH24:MI:SS')

But I have created a column and now I would like to update it on the records rather than a query.

All you need a `CASE` statement to update your records. See here https://stackoverflow.com/questions/18760632/update-multiple-rows-using-case-when-oracle — XING, Dec 01 '19 at 12:28
Unrelated to your problem, but: **NEVER**, ever call `to_date()` on a value that is already a date. That will first convert the `date` value to a `varchar` just to convert that `varchar` back to a `date` which it was to begin with. — , Dec 01 '19 at 12:42
for some reason my colunm time_start is varchar2 not date column — paolo ricardos, Dec 01 '19 at 12:50

score 0 · Accepted Answer · answered Dec 01 '19 at 12:42

You can use to_char() and a case expression:

update mytable
    set area = (case when to_char(start_time, 'HH24:MI') >= '06:00' and to_char(start_time, 'HH24:MI') < '10:00'
                     then 'A'
                     when to_char(start_time, 'HH24:MI') >= '10:00' and to_char(start_time, 'HH24:MI') < '15:30'
                     then 'B'
                     when to_char(start_time, 'HH24:MI') >= '15:30' and to_char(start_time, 'HH24:MI') < '22:00'
                     then 'C'
                end);

score 0 · Answer 2 · answered Dec 01 '19 at 13:25

my mistake was that my time_start column is varchar2, not date column, so with this code it worked

update myTable 
    set area = (case when substr(time_start,10,8) >= '06:00:00' and substr(time_start,10,8) <= '06:59:59' then 'A' 
                     when substr(time_start,10,8) >= '07:00:00' and substr(time_start,10,8) <= '07:59:59' then 'B' 
                     when substr(time_start,10,8) >= '08:00:00' and substr(time_start,10,8) <= '08:59:59' then 'C' 
end);

–

thanks

Update column based between two times Daily

2 Answers2