date question in oracle

Select ... WHERE currentime BETWEEN starttime AND endtime.

Try a case:

case when to_char(current_date,'HH12:MI:SS AM') between to_char(start_time,'HH12:MI:SS AM') starttime and to_char(end_time,'HH12:MI:SS AM') then 'is between' else 'not between' end

If 3 fields are date datatype, you can just use BETWEEN, and you can use TRUNC function to get just the time from a date datetime, if you want just to compare times and not dates::

select to_char(current_date,'HH12:MI:SS AM') currenttime,
       start_time,
         to_char(start_time,'HH12:MI:SS AM') starttime,
       end_time,
         to_char(end_time,'HH12:MI:SS AM') endtime
  from blood_drives
 where site_code = 'A397'
   and drive_date = trunc(sysdate)
   and trunc(current_date) between trunc(starttime) and trunc(endtime);

Select allOpen in new window

hope it helps.

please read up this article:
https://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html

I don't believe that truncating is a viable solution.

If start_time is 1/7/2013 09:10:00 and end_time is 1/7/2013 17:00:00 and current_date is 1/7/2013 05:00:00, then with the truncates, the record will be returned when it should not.

Fellow Experts,

If you read a few of the askers many previous questions you will see the times in question are stored as varchar2.  It has been also been pointed out many times about the flaws with this design.

If they are character fields, then how are you doing a TO_CHAR on a character field with a date format?  That doesn't work.

My start_time and end_time in the database is a date column.

I stand corrected.

Are you wanting the see if the dates are between or just select the dates that are between?

If just checking, tweak my case statement:

case when current_date between start_time and end_time then 'is between' else 'not between' end

I am checking to see if the current time is between start_time and end_time. Thanks for your case statement.

You are right. Truncate gets the opposite: it gets the date instead of the time. Please try this and sorry for my initial mistake:

select to_char(current_date,'HH12:MI:SS AM') currenttime,
       start_time,
         to_char(start_time,'HH12:MI:SS AM') starttime,
       end_time,
         to_char(end_time,'HH12:MI:SS AM') endtime
  from blood_drives
 where site_code = 'A397'
   and drive_date = trunc(sysdate)
   and current_date - trunc(current_date) between starttime - trunc(starttime) and endtime - trunc(endtime);

Select allOpen in new window

So, are we full circle back to my first between post, 38750972?

dvz: not exactly. What I'm doing by substracting the truncate from a date is to get its time part.

>>So, are we full circle back to my first between post, 38750972?

Based on http:#a38751396, they want a check of all rows not return the rows between the dates.  Hence the case statement.

Ok, if author doesn't want to filter, then your adding the case you suggested could be a better solution

Open C5;
Fetch C5 into  v_current_date,
               v_currenttime,
       v_start_time,
       v_starttime,
       v_end_time,
       v_endtime;
Close C5;
Can the case statement be added before I close C5 ot better to pass into a variable like v_accepted or v_not_accepted

 case
  when v_current_date between v_start_time and v_end_time
   then  v_accpeted = 'is between'
   else v_not_accepted = 'not between'
 end;

case should be inside your select

I agree it should be part of the select but you 'could' add it to the code but more like this:

v_accepted := case
  when v_current_date between v_start_time and v_end_time
   then  'is between'  else 'not between'
 end;

anumoses: what do you want to do ? If what you want is to filter, I would use the select I proposed. If what do you wnat is to take an action or another depending on if the time is between or not the two dates, then use the case-when as slightwv suggested.

select current_date,
       to_char(current_date,'HH12:MI:SS AM') currenttime,
       start_time,
       to_char(start_time,'HH12:MI:SS AM') starttime,
       end_time,
       to_char(end_time,'HH12:MI:SS AM') endtime
  from blood_drives
 where site_code = 'A397'
   and drive_date = trunc(sysdate)

CURRENT_DATE|CURRENTTIME|START_TIME|STARTTIME|END_TIME|ENDTIME
1/7/2013 1:52:16 PM|01:52:16 PM|1/7/2013 12:30:00 PM|12:30:00 PM|1/7/2013 7:00:00 PM|07:00:00 PM


If :qc_equipment_used2.equip_code is not null then
Open C5;
Fetch C5 into  v_current_date,
               v_currenttime,
        v_start_time,
        v_starttime,
       v_end_time,
        v_endtime;
v_accepted := case
                 when v_currenttime between v_starttime and v_endtime
                 then  'is between'  else 'not between'
                end;
    message('v accepted ='||v_accepted);pause;
Message is passing not between.

>>to_char(start_time,'HH12:MI:SS AM') starttime

You are comparing strings not dates.  Change the case to use the DATE variables not the VARCHAR2 variables.

I would really add it to the select...then you don't have to do the 'extra' if statement.

Try to print out the three values (of the three variables) so we can see why is not working

slightwv: you are right, but string comparision should work too on this case, just change de HH12 by HH24 and remove the trail AM so you will have a string with time in 24 hour format..

Also, using my approaching could be a good solution too:

select current_date,
       to_char(current_date,'HH12:MI:SS AM') currenttime,
       start_time,
       to_char(start_time,'HH12:MI:SS AM') starttime,
       end_time,
       to_char(end_time,'HH12:MI:SS AM') endtime
  from blood_drives
 where site_code = 'A397'
   and drive_date = trunc(sysdate)

CURRENT_DATE|CURRENTTIME|START_TIME|STARTTIME|END_TIME|ENDTIME
1/7/2013 1:52:16 PM|01:52:16 PM|1/7/2013 12:30:00 PM|12:30:00 PM|1/7/2013 7:00:00 PM|07:00:00 PM


If :qc_equipment_used2.equip_code is not null then
Open C5;
Fetch C5 into  v_current_date,
               v_currenttime,
        v_start_time,
        v_starttime,
       v_end_time,
        v_endtime;
v_accepted := case
                 when v_current_date - trunc(v_current_date) between v_start_time - trunc(v_start_time) and v_endtime - trunc(v_end_time)
                 then  'is between'  else 'not between'
                end;
    message('v accepted ='||v_accepted);pause;
Message is passing not between. 

Select allOpen in new window

>>could be a good solution too:

It might work (and probably does, I didn't run it) but why do a lot of extra steps that you don't need to do?

because is better to compare dates instead of strings.

>>because is better to compare dates instead of strings.

I agree.  That is why I stated that in http:#a38752322 and posted a simplified example in http:#a38752348

They compare dates w/o all the trunc and subtraction.

This assumes the 'day' portions work out.  If not, you will need them.

but I think the author wants to compare just the time of the dates, and your solution is comparing the date (with time, but with date also). This is why I suggest to use trunc.

For example, in your solution this example:
12/12/2012 3:00 between 24/24/2012 1:00 and 24/24/2012 6:00 won't match, and should match as time 3:00 is between 1:00 and 6:00. On my solution this example should work.

If the day portion of the dates don't match, why would you want the result just based on times?

We'll let anumoses decide to final solution.  I don't want to hijack this thread.

I don't know, but I think is what author is requesting. I agree, let's wait for the author decide. Anyway I think both have contributed to the solution.

This worked for me

select drive_date,current_date,
       to_char(current_date,'HH24:MI') currenttime,
       start_time,
       to_char(start_time,'HH24:MI') starttime,
       end_time,
       to_char(end_time,'HH24:MI') endtime,
       case when to_char(current_date,'HH24:MI')
         between to_char(start_time,'HH24:MI') and to_char(end_time,'HH24:MI')
         then 'in between'
         else 'not in between'
         end is_between         
  from blood_drives
  where drive_date = trunc(sysdate)
--------------------------------------
Attached results
results.txt

Is it possible that the start and end times can cross over day boundaries?  If so, that solution will not work, as shown here:

SQL> create table blood_drives
  2  ( cdate date,
  3    start_time date,
  4    end_time date);

Table created.

SQL> insert into blood_drives values
  2  (to_date('01082013010000','mmddyyyyhh24miss'),to_date('01072013170000','mmddyyyyhh24mi
24miss'));

1 row created.

SQL> commit;

Commit complete.

SQL> select cdate,
  2         to_char(cdate,'HH24:MI') currenttime,
  3         start_time,
  4         to_char(start_time,'HH24:MI') starttime,
  5         end_time,
  6         to_char(end_time,'HH24:MI') endtime,
  7         case when to_char(cdate,'HH24:MI')
  8           between to_char(start_time,'HH24:MI') and to_char(end_time,'HH24:MI')
  9           then 'in between'
 10           else 'not in between'
 11           end is_between
 12    from blood_drives;

CDATE               CURRE START_TIME          START END_TIME            ENDTI
------------------- ----- ------------------- ----- ------------------- -----
IS_BETWEEN
--------------
01/08/2013 01:00:00 01:00 01/07/2013 17:00:00 17:00 01/08/2013 06:00:00 06:00
not in between

Select allOpen in new window

The date is between the start end end time, but you are getting a not between result.

I found that output a little confusing as I was displaying the times with the date fields.  Here it is again without the times shown in the date fields.

SQL> select cdate,
  2         to_char(cdate,'HH24:MI') currenttime,
  3         start_time,
  4         to_char(start_time,'HH24:MI') starttime,
  5         end_time,
  6         to_char(end_time,'HH24:MI') endtime,
  7         case when to_char(cdate,'HH24:MI')
  8           between to_char(start_time,'HH24:MI') and to_char(end_time,'HH24:MI')
  9           then 'in between'
 10           else 'not in between'
 11           end is_between
 12    from blood_drives;

CDATE      CURRE START_TIME START END_TIME   ENDTI IS_BETWEEN
---------- ----- ---------- ----- ---------- ----- --------------
01/08/2013 01:00 01/07/2013 17:00 01/08/2013 06:00 not in between

Select allOpen in new window

the result is correct, as 1:00 is not between 17:00 and 6:00. Or maybe author wants to compare also dates ? If so, just compare date datetype, but I think author wants to compare just the time.

But is that what the author expects.  In fact, nothing is between 17:00 and 06:00.  I was just trying to point out that the solution being used would not work if that situation occurs within the data.

thanks