Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Time Difference
Oracle Time Difference [message #256589] Mon, 06 August 2007 01:30 Go to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
Hello,


Oracle 9.2.0.7, Unix sun solaris

ID	ACT_DATE		START_DATE	FINISH_DATE

DD1	1/01/2006	2/01/2006 12:03:08 PM	2/01/2006 12:03:09 PM
DD1	2/01/2006	3/01/2006 8:30:59 AM	3/01/2006 8:31:01 AM
DD1	3/01/2006	4/01/2006 3:24:41 PM	4/01/2006 3:24:44 PM
DD1	4/01/2006	5/01/2006 5:15:51 PM	5/01/2006 5:15:52 PM
DD1	5/01/2006	6/01/2006 9:48:51 AM	6/01/2006 9:48:53 AM
DD1	6/01/2006	7/01/2006 10:53:50 AM	7/01/2006 10:53:51 AM
WW1	1/01/2006	2/01/2006 7:59:14 PM	2/01/2006 7:59:49 PM
WW1	2/01/2006	3/01/2006 10:05:21 PM	3/01/2006 10:05:51 PM
WW1	3/01/2006	4/01/2006 9:17:40 PM	4/01/2006 9:18:22 PM
WW1	4/01/2006	5/01/2006 9:20:48 PM	5/01/2006 9:21:25 PM
WW1	5/01/2006	6/01/2006 9:30:24 PM	6/01/2006 9:30:53 PM
WW1	6/01/2006	7/01/2006 7:59:27 PM	7/01/2006 8:00:03 PM


select round(((b.finish_date - a.start_date)*24),2) hours
from 
(select start_date from T1 where  act_date >= '01jun2006' and id = 'WW1') a,
(select finish_date from T1 where  act_date >= '01jun2006' and id = 'DD1') b

What's wrong with above query?



Thanks

[Updated on: Mon, 06 August 2007 01:30]

Report message to a moderator

Re: Oracle Time Difference [message #256590 is a reply to message #256589] Mon, 06 August 2007 01:31 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>What's wrong with above query?
Nothing, everything, I give up; you tell me.
Re: Oracle Time Difference [message #256594 is a reply to message #256590] Mon, 06 August 2007 01:39 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
As far as i know if you enclose anything within quotes in oracle it is a character string. Having said that oracle is clever enough and it checks the datatype of the column which is involved and it is doing an implicit conversion if it can.

I got this from oracle documentation (SQL REFERENCE)

Quote:

Implicit and Explicit Data Conversion
Oracle recommends that you specify explicit conversions, rather than rely on implicit or automatic conversions, for these reasons:

SQL statements are easier to understand when you use explicit datatype conversion functions.

Implicit datatype conversion can have a negative impact on performance, especially if the datatype of a column value is converted to that of a constant rather than the other way around.

Implicit conversion depends on the context in which it occurs and may not work the same way in every case. For example, implicit conversion from a datetime value to a VARCHAR2 value may return an unexpected year depending on the value of the NLS_DATE_FORMAT parameter.

Algorithms for implicit conversion are subject to change across software releases and among Oracle products. Behavior of explicit conversions is more predictable.




I believe now you know what the problem is.

Regards

Rajaram

[Updated on: Mon, 06 August 2007 01:41] by Moderator

Report message to a moderator

Re: Oracle Time Difference [message #256595 is a reply to message #256589] Mon, 06 August 2007 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is it supposed to give?

Regards
Michel
Re: Oracle Time Difference [message #256596 is a reply to message #256590] Mon, 06 August 2007 01:42 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
With over 200 messages one would expect that you have learnt how to ask a question. But ok, I'll bite. This is what I want from you.

1. A table definition. What does the table look like? Data types etc...
2. Your expected results. And why you were expecting them and where it went wrong.

Basically, I want you to read the sticky here in the Newbies forum. The TIPS and TRICKS that is.

MHE
Re: Oracle Time Difference [message #256698 is a reply to message #256596] Mon, 06 August 2007 06:58 Go to previous messageGo to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
MHE

Quote:


T1(
ID VARCAHR2(10)
ACT_DATE DATE
START_DATE DATE
FINISH_DATE DATE)

When i run below query, i am getting not valid number error on a.start_date..

select round(((b.finish_date - a.start_date)*24),2) hours
from
(select start_date from T1 where act_date >= '01jun2006' and id = 'WW1') a,
(select finish_date from T1 where act_date >= '01jun2006' and id = 'DD1') b

basically i want to calculate time difference in hours where start_date is where id = WW1 and finish_Date where id = DD1

for each act_date.




Thanks

[Updated on: Mon, 06 August 2007 07:10] by Moderator

Report message to a moderator

Re: Oracle Time Difference [message #256703 is a reply to message #256698] Mon, 06 August 2007 07:03 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
did you read S.Rajaram's post? It (very, very strongly) hints at the solution.
Re: Oracle Time Difference [message #256722 is a reply to message #256698] Mon, 06 August 2007 08:01 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
And just to add to Maaher's post, 200+ messages and you still do not know the difference between a VARCHAR2 and a DATE column.

Please, please go into another line of business that you are more suited for.

[Updated on: Mon, 06 August 2007 08:01]

Report message to a moderator

Re: Oracle Time Difference [message #257004 is a reply to message #256722] Tue, 07 August 2007 06:49 Go to previous messageGo to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
joy_division

"Please, please go into another line of business that you are more suited for. "


Should not you be shamed making this comments, if i start asking you oracle problem, you wont even think about it.

so plz be mature , share your knowledge if you have any answer, dont be stupid.

rules are same for everyone, so please please dont make this kind of non-cooperative comments in future.

to_date (date, 'format'), i know it ... dont waste your time
date = '1jun2006'

error is about 2 queries used as source to perform diff operations, that's i am getting problem.



forgive you.

enjoy.
Re: Oracle Time Difference [message #257013 is a reply to message #257004] Tue, 07 August 2007 07:04 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Quote:
to_date (date, 'format'), i know it ... dont waste your time
date = '1jun2006'

error is about 2 queries used as source to perform diff operations, that's i am getting problem.



This makes no sense. Please reword what you are trying to say.
Re: Oracle Time Difference [message #257018 is a reply to message #257013] Tue, 07 August 2007 07:11 Go to previous messageGo to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
Pabolee


select round(((b.finish_date - a.start_date)*24),2) hours
from 
(select start_date from T1 where  act_date >= to_Date('01/06/2006','dd/mm/yyyy') and id = 'WW1') a,
(select finish_date from T1 where  act_date >= to_date('01/06/2006','dd/mm/yyyy') and id = 'DD1') b
where a.act_date = b.act_date;

now when i run this, i am getting invalid number error on
a.start_date



Thanks

[Updated on: Tue, 07 August 2007 07:13]

Report message to a moderator

Re: Oracle Time Difference [message #257019 is a reply to message #257004] Tue, 07 August 2007 07:13 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
@mymot:
NO IM SPEAK

"plz" is not a word.

@all: In this thread I only want to see useful replies or I'll see it closed.


On topic:
If you know about casting dates, you should use it. There's no excuse for implicit casting.

I'll see what I can come up with.

MHE
Re: Oracle Time Difference [message #257021 is a reply to message #257004] Tue, 07 August 2007 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
An answer was clearly given to you.
You post the same thing on AskTom and he gave you the same answer.
Quote:
to_date (date, 'format'), i know it ...

So why do you use it and make the same old error.

You insist in your errors.
I think I agree with joy_division and if you don't like our answer "so please please dont make this kind of" silly things.

Btw, we still don't understand what is your problem/error. Maybe you should also learn how to ask.

Regards
Michel


Re: Oracle Time Difference [message #257024 is a reply to message #257021] Tue, 07 August 2007 07:19 Go to previous messageGo to next message
mymot
Messages: 225
Registered: July 2005
Senior Member
Michel


Dont know what to say on your non-value adding , silly comments, if you dont want to contribute please dont waste your time.

BTW, you have not understood problem, where did you answer??
Appreciated.

MHE, suggested previously, Please, i beg you.

[Updated on: Tue, 07 August 2007 07:20]

Report message to a moderator

Re: Oracle Time Difference [message #257026 is a reply to message #257024] Tue, 07 August 2007 07:26 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Give me a SQL*Plus output of the following command:
DESC t1


This is all information we got from you (and I got the feeling that we must be missing something):
CREATE TABLE t1 ( ID          VARCHAR2(10)
                       , ACT_DATE    DATE
                       , START_DATE  DATE
                       , FINISH_DATE DATE
                       )
/

INSERT INTO t1
VALUES      ('DD1'
           , TO_DATE ('1/01/2006', 'mm/dd/yyyy')
           , TO_DATE ('2/01/2006 12:03:08 PM', 'mm/dd/yyyy HH:MI:SS AM')
           , TO_DATE ('2/01/2006 12:03:09 PM', 'mm/dd/yyyy HH:MI:SS AM')
            );
INSERT INTO t1
VALUES      ('DD1'
           , TO_DATE ('2/01/2006', 'mm/dd/yyyy')
           , TO_DATE ('3/01/2006 8:30:59 AM', 'mm/dd/yyyy HH:MI:SS AM')
           , TO_DATE ('3/01/2006 8:31:01 AM', 'mm/dd/yyyy HH:MI:SS AM')
            );
INSERT INTO t1
VALUES      ('DD1'
           , TO_DATE ('3/01/2006', 'mm/dd/yyyy')
           , TO_DATE ('4/01/2006 3:24:41 PM', 'mm/dd/yyyy HH:MI:SS AM')
           , TO_DATE ('4/01/2006 3:24:44 PM', 'mm/dd/yyyy HH:MI:SS AM')
            );
INSERT INTO t1
VALUES      ('DD1'
           , TO_DATE ('4/01/2006', 'mm/dd/yyyy')
           , TO_DATE ('5/01/2006 5:15:51 PM', 'mm/dd/yyyy HH:MI:SS AM')
           , TO_DATE ('5/01/2006 5:15:52 PM', 'mm/dd/yyyy HH:MI:SS AM')
            );
INSERT INTO t1
VALUES      ('DD1'
           , TO_DATE ('5/01/2006', 'mm/dd/yyyy')
           , TO_DATE ('6/01/2006 9:48:51 AM', 'mm/dd/yyyy HH:MI:SS AM')
           , TO_DATE ('6/01/2006 9:48:53 AM', 'mm/dd/yyyy HH:MI:SS AM')
            );
INSERT INTO t1
VALUES      ('DD1'
           , TO_DATE ('6/01/2006', 'mm/dd/yyyy')
           , TO_DATE ('7/01/2006 10:53:50 AM', 'mm/dd/yyyy HH:MI:SS AM')
           , TO_DATE ('7/01/2006 10:53:51 AM', 'mm/dd/yyyy HH:MI:SS AM')
            );
INSERT INTO t1
VALUES      ('WW1'
           , TO_DATE ('1/01/2006', 'mm/dd/yyyy')
           , TO_DATE ('2/01/2006 7:59:14 PM', 'mm/dd/yyyy HH:MI:SS AM')
           , TO_DATE ('2/01/2006 7:59:49 PM', 'mm/dd/yyyy HH:MI:SS AM')
            );
INSERT INTO t1
VALUES      ('WW1'
           , TO_DATE ('2/01/2006', 'mm/dd/yyyy')
           , TO_DATE ('3/01/2006 10:05:21 PM', 'mm/dd/yyyy HH:MI:SS AM')
           , TO_DATE ('3/01/2006 10:05:51 PM', 'mm/dd/yyyy HH:MI:SS AM')
            );
INSERT INTO t1
VALUES      ('WW1'
           , TO_DATE ('3/01/2006', 'mm/dd/yyyy')
           , TO_DATE ('4/01/2006 9:17:40 PM', 'mm/dd/yyyy HH:MI:SS AM')
           , TO_DATE ('4/01/2006 9:18:22 PM', 'mm/dd/yyyy HH:MI:SS AM')
            );
INSERT INTO t1
VALUES      ('WW1'
           , TO_DATE ('4/01/2006', 'mm/dd/yyyy')
           , TO_DATE ('5/01/2006 9:20:48 PM', 'mm/dd/yyyy HH:MI:SS AM')
           , TO_DATE ('5/01/2006 9:21:25 PM', 'mm/dd/yyyy HH:MI:SS AM')
            );
INSERT INTO t1
VALUES      ('WW1'
           , TO_DATE ('5/01/2006', 'mm/dd/yyyy')
           , TO_DATE ('6/01/2006 9:30:24 PM', 'mm/dd/yyyy HH:MI:SS AM')
           , TO_DATE ('6/01/2006 9:30:53 PM', 'mm/dd/yyyy HH:MI:SS AM')
            );
INSERT INTO t1
VALUES      ('WW1'
           , TO_DATE ('6/01/2006', 'mm/dd/yyyy')
           , TO_DATE ('7/01/2006 7:59:27 PM', 'mm/dd/yyyy HH:MI:SS AM')
           , TO_DATE ('7/01/2006 8:00:03 PM', 'mm/dd/yyyy HH:MI:SS AM')
            );

--
-- Improved query by mymot
--
SELECT ROUND (((b.finish_date - a.start_date) * 24), 2) hours
FROM   (SELECT start_date
        FROM   t1
        WHERE  act_date >= TO_DATE ('01/06/2006', 'dd/mm/yyyy') AND ID = 'WW1') a
     , (SELECT finish_date
        FROM   t1
        WHERE  act_date >= TO_DATE ('01/06/2006', 'dd/mm/yyyy') AND ID = 'DD1') b
/

DROP TABLE t1
/

Based on that information, I see no reason for an invalid number error. Can you confirm that the above script works on your box?

MHE
Re: Oracle Time Difference [message #257027 is a reply to message #257024] Tue, 07 August 2007 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maarten, I agree better close this useless topic.

mymot, if you don't like our answer, stop posting.
If you don't want to respect others using IM speak, stop posting.

I close the topic.

Regards
Michel
Re: Oracle Time Difference [message #257182 is a reply to message #257026] Tue, 07 August 2007 13:06 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maarten,

Your post at least served OP to post a correct example to Tom Kyte.
I'm sure you'd be glad to know he posted it at:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2200571416651#392419000346952802

Regards
Michel
Previous Topic: special characters in between the string
Next Topic: Daylight Savings time
Goto Forum:
  


Current Time: Thu Dec 08 08:21:24 CST 2016

Total time taken to generate the page: 0.15650 seconds