Home » SQL & PL/SQL » SQL & PL/SQL » Date Minus Time Query
Date Minus Time Query [message #285329] Tue, 04 December 2007 04:42 Go to next message
aslme
Messages: 25
Registered: May 2006
Location: Pakistan
Junior Member

Dear
I have a query

DESC REGISTER

NAME         TYPE
---------    ---------- 
EMP_ID       NUMBER(10)
EMPCODE      VARCHAR(10) 
TIME_IN      DATE
DUTYTIME_IN  VARCHAR2(10) 
TIMEIN_DIFF  NUMBER(5)

SELECT EMP_ID, EMPCODE, TIME_IN , DUTYTIME_IN, TIMEIN_DIFF
FROM REGISTER;


Result Shows

EMP_ID EMPCODE   TIME_IN   DUTYTIME TIMEIN_DIFF
------ --------  --------- -------- -----------
194    000194    27-NOV-07 08:30:55
27     000027    27-NOV-07 08:30:55
55     000055    27-NOV-07 08:30:55


I want the Diffrence in TIME_IN and DUTYTIME
when i use to_char to conver TIME_IN(Date) into TIME then use minus it show error and i am unable to run

Please can any one help me in this query.


Thanks

Cheers

[Updated on: Tue, 04 December 2007 05:00] by Moderator

Report message to a moderator

Re: Date Minus Time Query [message #285334 is a reply to message #285329] Tue, 04 December 2007 04:47 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, read the OraFAQ Forum Guide to learn how to properly format your message.

Regarding the fact that data you wrote is kind of messed up, how would you subtract '27-nov-07' and '08:30:55'. As a human, forget about Oracle - what would be the expected result of that operation?
Re: Date Minus Time Query [message #285339 is a reply to message #285329] Tue, 04 December 2007 04:54 Go to previous messageGo to next message
rumman
Messages: 48
Registered: June 2007
Location: Bangladesh
Member
Your question is not clear to me. You did not mention in what format you are storing your dutytime. However, assuming it as 24-hour format you may use the following query which will give you the result TIMEIN_DIFF in day.

---------------------
select r.*,
time_in - to_date(
to_char(time_in,'dd-mm-rr')||' '||dutytime,'dd-mm-rr hh24:mi:ss'
) timein_diff
from register r
----------------------

Inform me if it does not your desired solution.
Re: Date Minus Time Query [message #285341 is a reply to message #285329] Tue, 04 December 2007 05:00 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Create table and Insert table scripts will help to analysis your data.

Kiran.
Re: Date Minus Time Query [message #285345 is a reply to message #285329] Tue, 04 December 2007 05:05 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

SQL> select 'kiran'-'aithal' from dual;
select 'kiran'-'aithal' from dual
       *
ERROR at line 1:
ORA-01722: invalid number


How do you take difference of character values?

Kiran.

Re: Date Minus Time Query [message #285468 is a reply to message #285329] Tue, 04 December 2007 15:02 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Your design is at fault. If you are still developing, then redesign the table as follows

DESC REGISTER

NAME TYPE
--------- ----------
EMP_ID NUMBER(10)
EMPCODE VARCHAR(10)
TIME_IN DATE
time_out date

The time in and out will be accurate to the second. A date column always stores the complete date and time that is stored.
Previous Topic: trigge on server erro
Next Topic: I am facing Error,
Goto Forum:
  


Current Time: Fri Dec 09 19:49:01 CST 2016

Total time taken to generate the page: 0.12346 seconds