title: Oracle sql - date subtraction within a function
body: <p>(moved from previous post) - sorry if this is seen as a repeat!
  Hi everyone, 
  Just having some issues with date calculations and subtracting from a date within a function. </p>
  
  <p>I am confused with the data types I should be using as I am having to convert from date to_char for example. I'm not sure whether to have the return type as varchar2 or date?</p>
  
  <p>Here is the function that receives a car_id, looks in the table for that car, pulls out the cars arrive date, stores it in a date variable</p>
  
  <p>does the same with the departure date. </p>
  
  <p>Then converts both dates to_char, does the subtraction and returns it in a varchar2. </p>
  
  <p>When I call the function it will inputting the car_id as a parameter,
  then storing the returned result in a varchar variable, for a dbms output.
  eg.</p>
  
  <pre><code>v_result := get_duration('0001')
  </code></pre>
  
  <p>here is the function:</p>
  
  <pre><code>DROP FUNCTION get_duration;  
  CREATE FUNCTION get_duration (p_car_id number)  
  RETURN varchar2 is  
  v_arrive date;  
  v_depart date  
  v_duration varchar2(25); --not too sure about this variable choice  
  
  begin  
  
  select arrival, departure  
  into v_arrive, v_depart
  from car_info
  where car_id = p_car_id;
  
  v_duration := to_char(v_depart, 'dd-mon-yyyy hh24:mi:ss') - to_char(v_arrive, 'dd-mon-yyyy hh24:mi:ss')  
  return v_duration; 
   END;  
  / 
  </code></pre>
  
  <p>As you can see, I am trying to put the start and end times from the table in to the variables, then minus the end date from the start date.</p>
  
  <p>The function compiles, but with a warning, and when I got to call the function, the error: get_duration is invalid</p>
  
  <p>Any input on this would be greatly received,</p>
  
  <p>Sorry if the post is relatively big!</p>
  
  <p>regards,</p>
  
  <p>Darren</p>