Doubt with IBM DB2 rule

Question

Guys I’m having a hard time transcribing this sql server structure to IMB DB2. Could someone help me.

with cte as
(
select 
CASE
WHEN CONVERT(datetime, format( getdate(), 'HH:MM') )  BETWEEN '06:00' AND '11:59' then 1
WHEN CONVERT(datetime, format( getdate(), 'HH:MM') )  BETWEEN '12:00' AND '16:59' then 2
WHEN CONVERT(datetime, format( getdate(), 'HH:MM') )  BETWEEN '17:00' AND '23:59' then 3
WHEN CONVERT(datetime, format( getdate(), 'HH:MM') )  BETWEEN '00:00' AND '05:59' then 4
END AS regra_job
)
select * from cte

Answer 1

As I would add the minutes together with the time to the ibm db2 database. The code below is in SQL SERVER.

select * from vendas v where convert(date, DATAVENDA) = convert(date, getdate()) and CASE WHEN format( GETDATE(), 'HH:MM' ) BETWEEN '06:00' AND '11:59' then 1 WHEN format( GETDATE(), 'HH:MM' ) BETWEEN '12:00' AND '16:59' then 2 WHEN format( GETDATE(), 'HH:MM' ) BETWEEN '17:00' AND '23:59' then 3 WHEN format( GETDATE(), 'HH:MM' ) BETWEEN '00:00' AND '05:59' then 4 END = CASE WHEN CONVERT(datetime, format( DATAVENDA, 'HH:MM') ) BETWEEN '06:00' AND '11:59' then 1 WHEN CONVERT(datetime, format( DATAVENDA, 'HH:MM') ) BETWEEN '12:00' AND '16:59' then 2 WHEN CONVERT(datetime, format( DATAVENDA, 'HH:MM') ) BETWEEN '17:00' AND '23:59' then 3 WHEN CONVERT(datetime, format( DATAVENDA, 'HH:MM') ) BETWEEN '00:00' AND '05:59' then 4 END

Answer 2

I got a solution for Postgresql with a friend , but would like to find for ibm db2

select n.* from schema.tabela n where cast( (cast( n.datavenda as TIMESTAMP with time zone)) as date) = cast(CURRENT_TIMESTAMP as date) and CASE WHEN cast( to_char( CURRENT_TIMESTAMP, 'HH24:MM' ) as time) BETWEEN cast('06:00' as time) AND cast('11:59' as time) then 1 WHEN cast( to_char( CURRENT_TIMESTAMP, 'HH24:MM' ) as time) BETWEEN cast('12:00' as time) AND cast('16:59' as time) then 2 WHEN cast( to_char( CURRENT_TIMESTAMP, 'HH24:MM' ) as time) BETWEEN cast('17:00' as time) AND cast('23:59' as time) then 3 WHEN cast( to_char( CURRENT_TIMESTAMP, 'HH24:MM' ) as time) BETWEEN cast('00:00' as time) AND cast('05:59' as time) then 4 END = CASE WHEN cast( to_char((cast( n.datavenda as TIMESTAMP with time zone)), 'HH24:MM' ) as time) BETWEEN cast('06:00' as time) AND cast('11:59' as time) then 1 WHEN cast( to_char((cast( n.datavenda as TIMESTAMP with time zone)), 'HH24:MM' ) as time) BETWEEN cast('12:00' as time) AND cast('16:59' as time) then 2 WHEN cast( to_char((cast( n.datavenda as TIMESTAMP with time zone)), 'HH24:MM' ) as time) BETWEEN cast('17:00' as time) AND cast('23:59' as time) then 3 WHEN cast( to_char((cast( n.datavenda as TIMESTAMP with time zone)), 'HH24:MM' ) as time) BETWEEN cast('00:00' as time) AND cast('05:59' as time) then 4 END

Answer 3

This would be a way to get the same result.

with cte(regra_job) as
(values
    CASE
    WHEN HOUR(CURRENT_TIME) < 6 then 4
    WHEN HOUR(CURRENT_TIME) < 12 then 1
    WHEN HOUR(CURRENT_TIME) < 17 then 2
         ELSE 3
    END
)
select * from cte