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 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 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.
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
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