Oracle sql - 函数内的日期减法 [英] Oracle sql - date subtraction within a function

问题描述

（从上一篇文章中移除） - 抱歉，如果这被视为重复！
嗨大家，
只是有一些日期计算的问题，并从功能中的日期减去。

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

我对我应该使用的数据类型感到困惑，因为我不得不从日期to_char转换为例。我不知道是否将返回类型作为varchar2或date？

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?

这是收到car_id的功能，在表中查看该车，拉出汽车到达日期，将其存储在日期变量中

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

与出发日期相同。

然后将两个日期转换为_char，减法并将其返回到varchar2中。

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

当我调用该函数时，它将输入car_id作为参数，然后
将返回的结果存储在varchar变量中，用于dbms输出。
eg。

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')

这里是函数：

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.

该函数编译，但带有警告，当我调用该函数时，错误：get_duration无效

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,

比较大！

敬意，

Darren

推荐答案

琐碎的问题是，当您定义 v_depart ; >，并且在行尾，将值分配给 v_duration ;你正在混合你的变量名。 （您也不一致关于 car_info.id 的类型;您将它创建为 varchar 可能应该是一个数字，但这更多是对你以前的问题的评论）。

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.

从另一个减去一个日期给出一个数字值，这是天数;部分日子是分数，所以0.25是6小时。从您以前的问题的日期，这个查询：

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;

...显示持续时间为2.125，为2天3小时。

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

这篇关于Oracle sql - 函数内的日期减法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！