Solved

oracle date question

Posted on 2014-10-20
14
286 Views
Last Modified: 2014-10-20
select end_time,
to_char(end_time,'HH12:MI:SS AM') endtime,
start_time,
to_char(start_time,'HH12:MI:SS AM') starttime,
drive_date,drive_id
  from blood_drives
  where drive_date between '01-oct-2014' and '15-oct-2014'


END_TIME,ENDTIME,START_TIME,STARTTIME,DRIVE_DATE,DRIVE_ID

8/1/2012 2:30:00 PM,02:30:00 PM,8/1/2012 10:30:00 AM,10:30:00 AM,10/1/2014,2024984
8/1/2014 2:00:00 PM,02:00:00 PM,8/1/2014 10:00:00 AM,10:00:00 AM,10/1/2014,2025401
4/1/2012 3:00:00 PM,03:00:00 PM,4/1/2012 8:30:00 AM,08:30:00 AM,10/1/2014,2024089
8/1/2014 2:00:00 PM,02:00:00 PM,8/1/2014 10:00:00 AM,10:00:00 AM,10/1/2014,2025416
2/1/2008 7:30:00 PM,07:30:00 PM,9/1/2010 3:30:00 PM,03:30:00 PM,10/1/2014,2024983
12/1/2010 2:00:00 PM,02:00:00 PM,10/1/2012 10:00:00 AM,10:00:00 AM,10/1/2014,2023342
8/1/2014 3:30:00 PM,03:30:00 PM,8/1/2014 9:00:00 AM,09:00:00 AM,10/1/2014,2025327

But when I want to get difference in time I get error

select end_time,
to_char(end_time,'HH12:MI:SS AM') endtime,
start_time,
to_char(start_time,'HH12:MI:SS AM') starttime,
to_char(end_time,'HH12:MI:SS AM')  - to_char(start_time,'HH12:MI:SS AM') as diff,
drive_date,drive_id
  from blood_drives
  where drive_date between '01-oct-2014' and '15-oct-2014'

ORA-01722: invalid number

Help appreciated
0
Comment
Question by:anumoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
14 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40392275
Subtract the dates not the string values.

Don't use TO_CHAR and subtract them.

That is the same as:
SQL> select 'a'-'b' from dual;
select 'a'-'b' from dual
       *
ERROR at line 1:
ORA-01722: invalid number

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 40392296
I did to_char since I wanted to ignore the date which is not the correct date when compared to the drive_date. This is a very old program and the person who did this for some reason has a date that does not match to the drive_date bu the time poriton is correct. So I did to_char
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40392308
You cannot subtract characters no matter what the reason you did it.

If you don't want the number of days, just ignore the whole number part and take the fractional piece:
(end_time-start_time)-trunc(end_time-start_time)

That should return the fraction part of the day.  then it is basic math to compute whatever time you want.
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40392329
To see how many hours are between the time portion of two dates that are over a thousand years apart:

12:30 PM from 11:00AM is 1.5 hours.

I ignore the years...
drop table tab1 purge;
create table tab1(start_time date, end_time date);

insert into tab1 values(
to_date('01/01/1600 11:00:00','MM/DD/YYYY HH24:MI:SS'),
to_date('01/01/3459 12:30:00','MM/DD/YYYY HH24:MI:SS')
);
commit;

select ((end_time-start_time)- trunc(end_time-start_time))*24 from tab1;

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40392357
Taking a guess at a possible followup question:
If you want to break it down into hours and minutes, there are many ways to do this on the Internet.

This is the one I've seen most of the time:
https://asktom.oracle.com/pls/apex/ASKTOM.download_file?p_file=6551242712657900129

Just ignore the 'day' piece.
0
 
LVL 6

Author Comment

by:anumoses
ID: 40392370
CREATE TABLE TAB1
(
  END_TIME    DATE                              NOT NULL,
  ENDTIME     VARCHAR2(11 BYTE),
  START_TIME  DATE                              NOT NULL,
  STARTTIME   VARCHAR2(11 BYTE),
  DRIVE_DATE  DATE                              NOT NULL,
  DRIVE_ID    NUMBER(7)                         NOT NULL
)
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('08/01/2012 14:30:00', 'MM/DD/YYYY HH24:MI:SS'), '02:30:00 PM', TO_DATE('08/01/2012 10:30:00', 'MM/DD/YYYY HH24:MI:SS'), '10:30:00 AM', TO_DATE('10/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2024984);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('08/01/2014 14:00:00', 'MM/DD/YYYY HH24:MI:SS'), '02:00:00 PM', TO_DATE('08/01/2014 10:00:00', 'MM/DD/YYYY HH24:MI:SS'), '10:00:00 AM', TO_DATE('10/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2025401);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('04/01/2012 15:00:00', 'MM/DD/YYYY HH24:MI:SS'), '03:00:00 PM', TO_DATE('04/01/2012 08:30:00', 'MM/DD/YYYY HH24:MI:SS'), '08:30:00 AM', TO_DATE('10/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2024089);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('08/01/2014 14:00:00', 'MM/DD/YYYY HH24:MI:SS'), '02:00:00 PM', TO_DATE('08/01/2014 10:00:00', 'MM/DD/YYYY HH24:MI:SS'), '10:00:00 AM', TO_DATE('10/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2025416);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('02/01/2008 19:30:00', 'MM/DD/YYYY HH24:MI:SS'), '07:30:00 PM', TO_DATE('09/01/2010 15:30:00', 'MM/DD/YYYY HH24:MI:SS'), '03:30:00 PM', TO_DATE('10/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2024983);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('12/01/2010 14:00:00', 'MM/DD/YYYY HH24:MI:SS'), '02:00:00 PM', TO_DATE('10/01/2012 10:00:00', 'MM/DD/YYYY HH24:MI:SS'), '10:00:00 AM', TO_DATE('10/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2023342);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('08/01/2014 15:30:00', 'MM/DD/YYYY HH24:MI:SS'), '03:30:00 PM', TO_DATE('08/01/2014 09:00:00', 'MM/DD/YYYY HH24:MI:SS'), '09:00:00 AM', TO_DATE('10/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2025327);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('07/01/2014 13:30:00', 'MM/DD/YYYY HH24:MI:SS'), '01:30:00 PM', TO_DATE('07/01/2014 10:00:00', 'MM/DD/YYYY HH24:MI:SS'), '10:00:00 AM', TO_DATE('10/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2025216);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('07/01/2014 16:00:00', 'MM/DD/YYYY HH24:MI:SS'), '04:00:00 PM', TO_DATE('07/01/2014 12:00:00', 'MM/DD/YYYY HH24:MI:SS'), '12:00:00 PM', TO_DATE('10/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2025299);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('03/01/2007 14:00:00', 'MM/DD/YYYY HH24:MI:SS'), '02:00:00 PM', TO_DATE('03/01/2007 08:30:00', 'MM/DD/YYYY HH24:MI:SS'), '08:30:00 AM', TO_DATE('10/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2025033);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('09/01/2009 18:30:00', 'MM/DD/YYYY HH24:MI:SS'), '06:30:00 PM', TO_DATE('11/01/2006 14:00:00', 'MM/DD/YYYY HH24:MI:SS'), '02:00:00 PM', TO_DATE('10/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2023796);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('07/01/2013 12:30:00', 'MM/DD/YYYY HH24:MI:SS'), '12:30:00 PM', TO_DATE('07/01/2013 08:30:00', 'MM/DD/YYYY HH24:MI:SS'), '08:30:00 AM', TO_DATE('10/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2024586);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('11/01/2006 14:00:00', 'MM/DD/YYYY HH24:MI:SS'), '02:00:00 PM', TO_DATE('11/01/2006 08:00:00', 'MM/DD/YYYY HH24:MI:SS'), '08:00:00 AM', TO_DATE('10/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2024985);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('11/01/2006 19:30:00', 'MM/DD/YYYY HH24:MI:SS'), '07:30:00 PM', TO_DATE('11/01/2006 15:30:00', 'MM/DD/YYYY HH24:MI:SS'), '03:30:00 PM', TO_DATE('10/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2023436);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('11/01/2006 12:00:00', 'MM/DD/YYYY HH24:MI:SS'), '12:00:00 PM', TO_DATE('09/01/2013 08:00:00', 'MM/DD/YYYY HH24:MI:SS'), '08:00:00 AM', TO_DATE('10/03/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2024986);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('08/01/2014 17:00:00', 'MM/DD/YYYY HH24:MI:SS'), '05:00:00 PM', TO_DATE('08/01/2014 13:00:00', 'MM/DD/YYYY HH24:MI:SS'), '01:00:00 PM', TO_DATE('10/03/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2025313);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('08/01/2014 14:00:00', 'MM/DD/YYYY HH24:MI:SS'), '02:00:00 PM', TO_DATE('08/01/2014 10:00:00', 'MM/DD/YYYY HH24:MI:SS'), '10:00:00 AM', TO_DATE('10/03/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2025329);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('09/01/2007 13:50:00', 'MM/DD/YYYY HH24:MI:SS'), '01:50:00 PM', TO_DATE('09/01/2007 08:50:00', 'MM/DD/YYYY HH24:MI:SS'), '08:50:00 AM', TO_DATE('10/03/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2024988);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('07/01/2014 14:00:00', 'MM/DD/YYYY HH24:MI:SS'), '02:00:00 PM', TO_DATE('07/01/2014 09:30:00', 'MM/DD/YYYY HH24:MI:SS'), '09:30:00 AM', TO_DATE('10/04/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2025023);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('08/01/2011 14:00:00', 'MM/DD/YYYY HH24:MI:SS'), '02:00:00 PM', TO_DATE('08/01/2010 10:00:00', 'MM/DD/YYYY HH24:MI:SS'), '10:00:00 AM', TO_DATE('10/04/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2023322);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('07/01/2014 14:00:00', 'MM/DD/YYYY HH24:MI:SS'), '02:00:00 PM', TO_DATE('07/01/2014 10:00:00', 'MM/DD/YYYY HH24:MI:SS'), '10:00:00 AM', TO_DATE('10/04/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2025302);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('08/01/2009 11:30:00', 'MM/DD/YYYY HH24:MI:SS'), '11:30:00 AM', TO_DATE('11/01/2006 07:30:00', 'MM/DD/YYYY HH24:MI:SS'), '07:30:00 AM', TO_DATE('10/04/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2024779);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('06/01/2007 13:00:00', 'MM/DD/YYYY HH24:MI:SS'), '01:00:00 PM', TO_DATE('08/01/2009 09:00:00', 'MM/DD/YYYY HH24:MI:SS'), '09:00:00 AM', TO_DATE('10/04/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2024990);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('07/01/2014 14:00:00', 'MM/DD/YYYY HH24:MI:SS'), '02:00:00 PM', TO_DATE('07/01/2014 10:00:00', 'MM/DD/YYYY HH24:MI:SS'), '10:00:00 AM', TO_DATE('10/04/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2025292);
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('07/01/2014 13:30:00', 'MM/DD/YYYY HH24:MI:SS'), '01:30:00 PM', TO_DATE('07/01/2014 09:30:00', 'MM/DD/YYYY HH24:MI:SS'), '09:30:00 AM', TO_DATE('10/04/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2025213);
COMMIT;

Open in new window


When I take the difference I dont get the right values. And I know the date part is not correct. Let me know.
0
 
LVL 6

Author Comment

by:anumoses
ID: 40392394
The link is helpful
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40392396
>>When I take the difference I dont get the right values. And I know the date part is not correct. Let me know.

What are you getting and what do you want?
Pick one of the rows from the example you just posted and post what you get and what you want to get.

Since this one has a different date portion, let's use this one:
Insert into TAB1
   (END_TIME, ENDTIME, START_TIME, STARTTIME, DRIVE_DATE, 
    DRIVE_ID)
 Values
   (TO_DATE('02/01/2008 19:30:00', 'MM/DD/YYYY HH24:MI:SS'), '07:30:00 PM', TO_DATE('09/01/2010 15:30:00', 'MM/DD/YYYY HH24:MI:SS'), '03:30:00 PM', TO_DATE('10/01/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    2024983);

Open in new window


In the example I posted, do you not get 1.5?
0
 
LVL 6

Author Comment

by:anumoses
ID: 40392405
select end_time,
to_char(end_time,'HH12:MI:SS AM') endtime,
start_time,
to_char(start_time,'HH12:MI:SS AM') starttime,
((end_time-start_time)- trunc(end_time-start_time))*24  as diff,
  trunc( mod( (end_time-start_time)*24, 24 ) )  "Hr",
       trunc( mod( (end_time-start_time)*24*60, 60 ) )  "Mi",
 trunc( mod( (end_time-start_time)*24*60*60, 60 ) ) "Sec",
drive_date,drive_id
  from tab1
  where drive_id = 2024983

END_TIME,ENDTIME,START_TIME,STARTTIME,DIFF,Hr,Mi,Sec,DRIVE_DATE,DRIVE_ID
2/1/2008 7:30:00 PM,07:30:00 PM,9/1/2010 3:30:00 PM,03:30:00 PM,-20,-20,0,0,10/1/2014,2024983
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40392410
That is what you get.

Now what do you want?
0
 
LVL 6

Author Comment

by:anumoses
ID: 40392430
I need to get

END_TIME,ENDTIME,START_TIME,STARTTIME,DIFF,Hr,Mi,Sec,DRIVE_DATE,DRIVE_ID
2/1/2008 7:30:00 PM,07:30:00 PM,9/1/2010 3:30:00 PM,03:30:00 PM,4,4,0,0,10/1/2014,2024983
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40392457
Didn't know the end_time date portion could be older than the start_time date portion.

This should work in 9iR2:
drop table tab1 purge;
create table tab1(start_time date, end_time date);

Insert into TAB1
   (start_TIME, end_TIME)
 Values
   (
   TO_DATE('09/01/2010 15:30:00', 'MM/DD/YYYY HH24:MI:SS'),
   TO_DATE('02/01/2008 19:30:00', 'MM/DD/YYYY HH24:MI:SS'));

commit;

select 
  extract(day from numtodsinterval(to_date(to_char(end_time,'HH24:MI:SS'),'HH24:MI:SS')-to_date(to_char(start_time,'HH24:MI:SS'),'HH24:MI:SS'),'day')) days,
  extract(hour from numtodsinterval(to_date(to_char(end_time,'HH24:MI:SS'),'HH24:MI:SS')-to_date(to_char(start_time,'HH24:MI:SS'),'HH24:MI:SS'),'day')) hours,
  extract(minute from numtodsinterval(to_date(to_char(end_time,'HH24:MI:SS'),'HH24:MI:SS')-to_date(to_char(start_time,'HH24:MI:SS'),'HH24:MI:SS'),'day')) minutes,
  extract(second from numtodsinterval(to_date(to_char(end_time,'HH24:MI:SS'),'HH24:MI:SS')-to_date(to_char(start_time,'HH24:MI:SS'),'HH24:MI:SS'),'day')) seconds
from tab1
/

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 40392638
Thanks a lot. I so much depend on your and sdstuber's answers.
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 40392639
Thanks
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

615 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