Solved

date question in oracle

Posted on 2013-01-07
37
404 Views
Last Modified: 2013-01-09
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)

Is there a way to find if the currenttime is between starttime and endtime?
0
Comment
Question by:anumoses
  • 12
  • 11
  • 6
  • +3
37 Comments
 
LVL 23

Expert Comment

by:David
Comment Utility
Select ... WHERE currentime BETWEEN starttime AND endtime.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
0
 
LVL 15

Expert Comment

by:gplana
Comment Utility
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);

Open in new window


hope it helps.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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.
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
My start_time and end_time in the database is a date column.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I stand corrected.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
I am checking to see if the current time is between start_time and end_time. Thanks for your case statement.
0
 
LVL 15

Expert Comment

by:gplana
Comment Utility
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);

Open in new window

0
 
LVL 23

Expert Comment

by:David
Comment Utility
So, are we full circle back to my first between post, 38750972?
0
 
LVL 15

Expert Comment

by:gplana
Comment Utility
dvz: not exactly. What I'm doing by substracting the truncate from a date is to get its time part.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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.
0
 
LVL 15

Expert Comment

by:gplana
Comment Utility
Ok, if author doesn't want to filter, then your adding the case you suggested could be a better solution
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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;
0
 
LVL 15

Expert Comment

by:gplana
Comment Utility
case should be inside your select
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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;
0
 
LVL 15

Expert Comment

by:gplana
Comment Utility
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.
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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.
0
 
LVL 15

Expert Comment

by:gplana
Comment Utility
Try to print out the three values (of the three variables) so we can see why is not working
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
Here is a working test case that shows it:

drop table tab1 purge;
create table tab1(CURRENT_DATE date, START_TIME date, END_TIME date);

insert into tab1 values(
	to_date('1/7/2013 1:52:16 PM','MM/DD/YYYY HH:MI:SS AM'),
	to_date('1/7/2013 12:30:00 PM','MM/DD/YYYY HH:MI:SS AM'),
	to_date('1/7/2013 7:00:00 PM','MM/DD/YYYY HH:MI:SS AM')
);
commit;

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,
       case when current_date between start_time and end_time then 'in between' else 'not in between' end is_between
  from tab1
/

Open in new window

0
 
LVL 15

Expert Comment

by:gplana
Comment Utility
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. 

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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?
0
 
LVL 15

Expert Comment

by:gplana
Comment Utility
because is better to compare dates instead of strings.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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.
0
 
LVL 15

Expert Comment

by:gplana
Comment Utility
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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.
0
 
LVL 15

Expert Comment

by:gplana
Comment Utility
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.
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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

Open in new window


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

Expert Comment

by:johnsone
Comment Utility
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

Open in new window

0
 
LVL 15

Expert Comment

by:gplana
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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.
0
 
LVL 6

Author Closing Comment

by:anumoses
Comment Utility
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now