Solved

date question in oracle

Posted on 2013-01-07
37
415 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
ID: 38750972
Select ... WHERE currentime BETWEEN starttime AND endtime.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38750983
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
ID: 38751000
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38751118
0
 
LVL 35

Expert Comment

by:johnsone
ID: 38751282
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38751301
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 35

Expert Comment

by:johnsone
ID: 38751340
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
ID: 38751353
My start_time and end_time in the database is a date column.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38751359
I stand corrected.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38751388
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
ID: 38751396
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
ID: 38751429
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
ID: 38751436
So, are we full circle back to my first between post, 38750972?
0
 
LVL 15

Expert Comment

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

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38751464
>>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
ID: 38751473
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
ID: 38751726
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
ID: 38751755
case should be inside your select
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38751762
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
ID: 38751771
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
ID: 38752300
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38752322
>>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
ID: 38752332
Try to print out the three values (of the three variables) so we can see why is not working
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 38752348
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
ID: 38752359
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38752376
>>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
ID: 38752390
because is better to compare dates instead of strings.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38752413
>>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
ID: 38752439
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38752452
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
ID: 38752469
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
ID: 38754606
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 35

Expert Comment

by:johnsone
ID: 38756071
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 35

Expert Comment

by:johnsone
ID: 38756079
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
ID: 38756452
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 35

Expert Comment

by:johnsone
ID: 38756646
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
ID: 38758907
thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

679 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