Oracle sql - date subtraction within a function

Question

(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.

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?

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

does the same with the departure date.

Then converts both dates to_char, does the subtraction and returns it in a varchar2.

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.

v_result := get_duration('0001')

here is the function:

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;  
/ 

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.

The function compiles, but with a warning, and when I got to call the function, the error: get_duration is invalid

Any input on this would be greatly received,

Sorry if the post is relatively big!

regards,

Darren

Answer 1

Trivial problems are that you're missing a ; when you define v_depart, and at the end of the line you assign the value to v_duration; and you're mixing up your variable names. (You're also inconsistent about the type of car_info.id; you've created it as a varchar when it probably ought to be a number, but that's more of a comment on your previous question).

The main problem is that you can't perform a minus on two strings, as that doesn't really mean anything. You need to do the manipulation of the original dates, and then figure out how you want to return the result to the caller.

Subtracting one date from another gives a number value, which is the number of days; partial days are fractions, so 0.25 is 6 hours. With the dates from your previous quesiton, this query:

select arrival, departure, departure - arrival as duration
from car_info
where car_id = 1;

... shows duration of 2.125, which is 2 days and 3 hours.

This isn't the best way to do this, but to show you the process of what's going on I'll use that duration number and convert it into a string in quite a long-winded way:

CREATE OR REPLACE FUNCTION get_duration (p_car_id number)
RETURN varchar2 is
    v_arrive date;
    v_depart date;
    v_duration number;
    v_days number;
    v_hours number;
    v_minutes number;
    v_seconds number;
BEGIN

    select arrival, departure, departure - arrival
    into v_arrive, v_depart, v_duration
    from car_info
    where car_id = p_car_id;

    -- Days is the whole-number part, which you can get with trunc
    v_days := trunc(v_duration);
    -- Hours, minutes and seconds are extracted from the remainder
    v_hours := trunc(24 * (v_duration - v_days));
    v_minutes := trunc(60 * (v_duration - v_days - (v_hours/24)));
    v_seconds := trunc(60 * (v_duration - v_days - (v_hours/24)
        - (v_minutes/(24*60))));

    return v_days || ' days '
        || to_char(v_hours, '00') || ' hours '
        || to_char(v_minutes, '00') || ' minutes '
        || to_char(v_seconds, '00') || ' seconds';
END;
/

Function created.

show errors

No errors.

select get_duration(1) from dual;

GET_DURATION(1)
--------------------------------------------------------------------------------
2 days  03 hours  00 minutes  00 seconds

You can play with the number format masks etc. to get the output you want.

Answer 2

DATE arithmetic works on DATEs not on VARCHAR2s:

CREATE FUNCTION get_duration (p_car_id number)  
RETURN varchar2 is  
v_arrive date;  
v_depart date  
v_duration number; --<<<

begin  

select arrival, departure  
into v_arrive, v_depart
from car_info
where car_id = p_car_id;

v_duration := v_arrive - v_depart;
return v_duration; 
 END; 

That gives a result in days.

Answer 3

You cannot subtract dates once you cast them into varchar2 (to_char function does that!). Instead use v_duration := v_stop - v_start; which will give you the result 'v_duration' in days. Then you can convert it to hours, minutes, etc..