PL / SQL: convert varchar to time to get difference later

Keep in mind that Oracle does not have a type that represents only one hour. For this reason, when you use to_date(campo_hora, 'HH24:MI') (I assume something like that you should have done), Oracle can not only return the time, but it also returns a date. And by default, it will return the first day of the current month.

For example, if you execute the following statement, taking into account that the date today is 2016-11-25 :

select to_date('23:30', 'HH24:MI') from dual

... will return the following date: 2016-11-01 23:30:00 .

Now, if your goal is to compare the hour portion of sysdate with your field, you need to convert your field to a date that has the same day as sysdate so that the difference be alone with the time. This can be achieved in this way (assuming that campo_hora contains the value '23:30' ):

select to_date(to_char(sysdate, 'YYYY-MM-DD') || ' ' || campo_hora, 'YYYY-MM-DD HH24:MI')
  from dual

... assuming you execute the statement today ( 2016-11-25 ), the result will be the following date: 2016-11-25 23:30:00 .

Now you can compare that date with sysdate to get the difference in hours.

For example, now you can do:

select to_date(to_char(sysdate, 'YYYY-MM-DD') || ' ' || campo_hora, 'YYYY-MM-DD HH24:MI')
       - sysdate
  from dual

... which will return a decimal that represents the difference in number of days, something like: 0.60605... , that is, a fraction of a day.

Now the question is to know in what format you want to see the difference, since you did not specify it. But let's say you want to see the difference in hours, then you have to multiply by 24 :

select (to_date(to_char(sysdate, 'YYYY-MM-DD') || ' ' || campo_hora, 'YYYY-MM-DD HH24:MI')
       - sysdate) * 24
  from dual

... or if you want to see the difference in minutes, then you have to multiply by 24 * 60 :

select (to_date(to_char(sysdate, 'YYYY-MM-DD') || ' ' || campo_hora, 'YYYY-MM-DD HH24:MI')
       - sysdate) * 24 * 60
  from dual

... and like that for seconds too ...

Another option is that Oracle also has a type to represent intervals. It is called INTERVAL DAY TO SECOND . If you are interested in working with this guy, then you can make a query like the following:

select TO_DSINTERVAL('0 ' || campo_hora || ':00')
       - NUMTODSINTERVAL(sysdate - trunc(sysdate), 'day')
  from dual

If you execute the query at 9:40 am more or less, this will return a value similar to: +00 13:50:30.000000 , which represents a difference of 13 hours, 50 minutes, 30 seconds, etc.

What you can do is the following, if you have stored only the hour, minute and second in a field of your database, you would have to add the time in a to_date

to_date(to_char(sysdate,'dd/mm/yyyy')||campo_hora, 'dd/mm/yyyy hh24:mi:ss')    

This way, if you subtract it with the sysdate , Oracle will return it in days.

 select to_date(to_char(sysdate,'dd/mm/yyyy')||campo_hora, 'dd/mm/yyyy hh24:mi:ss') - sysdate 
from dual

Now it's a matter of whether you need the difference in hours, minutes or seconds. I leave the example to see in minutes.

select (to_date(to_char(sysdate,'dd/mm/yyyy')||campo_hora, 'dd/mm/yyyy hh24:mi:ss') - sysdate)*24*60, sysdate, to_date(to_char(sysdate,'dd/mm/yyyy')||campo_hora, 'dd/mm/yyyy hh24:mi:ss')
 from dual

You can truncate or round the result according to what you need. I hope you serve