anumoses
asked on
oracle date question
select end_time,
to_char(end_time,'HH12:MI: SS AM') endtime,
start_time,
to_char(start_time,'HH12:M I: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_TIM E,STARTTIM E,DRIVE_DA TE,DRIVE_I D
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:M I:SS AM') starttime,
to_char(end_time,'HH12:MI: SS AM') - to_char(start_time,'HH12:M I: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
to_char(end_time,'HH12:MI:
start_time,
to_char(start_time,'HH12:M
drive_date,drive_id
from blood_drives
where drive_date between '01-oct-2014' and '15-oct-2014'
END_TIME,ENDTIME,START_TIM
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:
start_time,
to_char(start_time,'HH12:M
to_char(end_time,'HH12:MI:
drive_date,drive_id
from blood_drives
where drive_date between '01-oct-2014' and '15-oct-2014'
ORA-01722: invalid number
Help appreciated
ASKER
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
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)-trun c(end_time -start_tim e)
That should return the fraction part of the day. then it is basic math to compute whatever time you want.
If you don't want the number of days, just ignore the whole number part and take the fractional piece:
(end_time-start_time)-trun
That should return the fraction part of the day. then it is basic math to compute whatever time you want.
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...
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;
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.
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.
ASKER
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;
When I take the difference I dont get the right values. And I know the date part is not correct. Let me know.
ASKER
The link is helpful
>>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:
In the example I posted, do you not get 1.5?
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);
In the example I posted, do you not get 1.5?
ASKER
select end_time,
to_char(end_time,'HH12:MI: SS AM') endtime,
start_time,
to_char(start_time,'HH12:M I: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*6 0, 60 ) ) "Mi",
trunc( mod( (end_time-start_time)*24*6 0*60, 60 ) ) "Sec",
drive_date,drive_id
from tab1
where drive_id = 2024983
END_TIME,ENDTIME,START_TIM E,STARTTIM E,DIFF,Hr, Mi,Sec,DRI VE_DATE,DR IVE_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,2 024983
to_char(end_time,'HH12:MI:
start_time,
to_char(start_time,'HH12:M
((end_time-start_time)- trunc(end_time-start_time)
trunc( mod( (end_time-start_time)*24, 24 ) ) "Hr",
trunc( mod( (end_time-start_time)*24*6
trunc( mod( (end_time-start_time)*24*6
drive_date,drive_id
from tab1
where drive_id = 2024983
END_TIME,ENDTIME,START_TIM
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,2
That is what you get.
Now what do you want?
Now what do you want?
ASKER
I need to get
END_TIME,ENDTIME,START_TIM E,STARTTIM E,DIFF,Hr, Mi,Sec,DRI VE_DATE,DR IVE_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,20249 83
END_TIME,ENDTIME,START_TIM
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,20249
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot. I so much depend on your and sdstuber's answers.
ASKER
Thanks
Don't use TO_CHAR and subtract them.
That is the same as:
Open in new window