-1

I am creating an Oracle fast formula, I need to check the time between 06:00 AM and 12:00 AM i.e. when the PAY_TIME > 06:00 AM and less than 12:00 AM.

I am using the following -

                    L_STARTmin  = TO_CHAR(AISTOPTIME, 'HH24:MI:SS')

But when I am comparing this to > 06:00:00 the condition is not entering the loop, I am using -

IF TO_DATE(L_STARTmin,'HH24:MI:SS')>= TO_DATE('06:00:00', 'HH24:MI:SS')AND TO_DATE(L_STOPmin,'HH24:MI:SS')<= TO_DATE('12:00:00', 'HH24:MI:SS')

How can i only compare the date part i.e. if time is 05:00:00 AM then this loop shouldnt be entered, else it should be, how to use the timestamp for this ?

SSA_Tech124
  • 577
  • 1
  • 9
  • 25

1 Answers1

0

You didn't say which datatype you used for l_startmin and l_stopmin. Should be date (or timestamp). Code you posted suggests different - a varchar2, which is wrong - don't store date(time) values in inappropriate datatype columns/variables.

How to extract hour from date value? sysdate returns date datatype so I'll use it for this example.

SQL> select sysdate,
  2         to_number(to_char(sysdate, 'hh24')) hour_1,
  3         extract(hour from cast(sysdate as timestamp)) hour_2
  4  from dual;

SYSDATE                 HOUR_1     HOUR_2
------------------- ---------- ----------
12.03.2023 09:13:46          9          9

SQL>

If datatype was varchar2, then a lot of TO_this and TO_that (first convert string to date, then apply to_char to extract hours, and to_number it so that you get a numeric representation of hour value):

SQL> select to_number(to_char(to_date('12.03.2023 09:15:23', 'dd.mm.yyyy hh24:mi:ss'), 'hh24')) hour_1
  2  from dual;

    HOUR_1
----------
         9

SQL>

As you aren't really interested in sub-hour information, code might evaluate to

if extract(hour from cast(l_startmin as timestamp)) >= 6 and
   extract(hour from cast(l_stopmin  as timestamp)) <= 12
then ...
Littlefoot
  • 131,892
  • 15
  • 35
  • 57