ansaurus

Question

Answer 1

+1 A:

If you are using 9i above does this give you the expected result?

select extract(hour from numtodsinterval(to_date('14:00:00','HH24:MI:SS') - to_date('13:15:00','HH24:MI:SS'),'DAY'))
||':'|| extract(minute from numtodsinterval(to_date('14:00:00','HH24:MI:SS') - to_date('13:15:00','HH24:MI:SS'),'DAY')) diff
from dual
/

DIFF
------------------

0:45

Extract is detailed here

and numtodsinterval is detailed here

carpenteri 2010-05-05 20:12:55

Thank you very much for the fast solution. Now I am strugling with my own data isues I think. I am getting this error: "ORA-01858: a non-numeric character was found where a numeric was expected" - The dateTime field looks like this: 8/12/2009 14:29:59 - I don't get it

Steve 2010-05-05 20:24:22

Glad I could help - Instead of to_date('14:00:00','HH24:MI:SS')how about using to_date('8/12/2009 14:29:59', 'MM/DD/YYYY HH24:MI:SS')I don't know if I have got the month (MM) and the day (DD) in the correct position but you should be able to swap these

carpenteri 2010-05-05 20:33:22

Thanks again, I did just that but the weird thing is if I hardcode the date/time into the function it works, when I get it from the table I got that error. The date is copied from the table, how can that be....anyway, you already help me so much. Thank you again.

Steve 2010-05-05 20:45:00

if you run this query: select valuefrom nls_session_parameterswhere parameter = 'NLS_DATE_FORMAT' what date format do you get back? if it is different to what you expect and the database setting try using ALTER SESSION SET NLS_DATE_FORMAT='YOUR DATE FORMAT'

carpenteri 2010-05-05 20:58:18

I get this: DD-MON-RR

Steve 2010-05-05 21:00:18

I don't have toad so if you run this command in sql plus what do you get? show parameter nls_date_format

carpenteri 2010-05-05 21:03:07

That did it but now the DIFF = 0.0 - Should be 0.75 - My STart = 8/12/2009 13:15:00 - endtime = 8/12/2009 14:29:59

Steve 2010-05-05 21:06:26

Sorry man, I did not properly set the NLS format, after setting it correctly its all good. Sorry about that. This was a great learning experience.

Steve 2010-05-05 21:15:14

No worries and again glad I could help!

carpenteri 2010-05-05 21:17:17

ansaurus

tags:

views:

answers:

PL/SQL time segment calculation

related questions