Date format problem

From: <qenr_at_vara.nl>
Date: Mon, 11 Oct 1999 12:07:08 GMT
Message-ID: <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.? Received on Mon Oct 11 1999 - 14:07:08 CEST

Original text of this message