Re: Date format problem

Your first query is correct but the result is in day. See:

select 0.000115740740740741 * 86400 from dual;

0.000115740740740741*86400

                        10

1 row selected.

When you multiply the result by the number of seconds in a day (86400) you get your 10 seconds.

--
Regards

Michel


<qenr_at_vara.nl> a écrit dans le message : 3801c7ec.17497662_at_news.omroep.nl...


> Hi all,

> And thanks in advance for any suggestions as how to solve this

> problem.

>

>

> I have a remote database with a couple of date/time columns in

> varchar2 with the following format:  'yyyymmddhh24miss'  for example

> (19990901161102)

>

> Now I want to convert this  date/time format type back and compare

> them with my own date/time format types. I also want to subtract the

> different collumns to see if there is a difference in time or dates.

>

> I made the following query. But when I subtract the two date/time

> columns I don't get the result I want.

>

>

> select to_date('19990901160910', 'yyyymmddhh24miss') -

> to_date('19990901160900' , 'yyyymmddhh24miss')

> from sys.dual;

>

> The result is 0,000115740740740741 wich is not the answer.

> (which is 10 seconds). To make things worse the differences in

> date/time in the query result in varying numbers (i.e. sometimes the

> differince is only 10 seconds sometimes the difference is a 12 hrs 15

> min and 35 sec.

>

> for example

> (2-9-99 0:13:40) - (1-9-99 0:13:40)

>

>

> I tried all kinds of queries (including substrings to get just the

> time in seconds, or to_number and back to to_date again etc etc) but

> nothing seems to work.

>

> What am I doing wrong.?