40055

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

Answer1:

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.

Answer2:

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.

Answer3:

You <strong>cannot</strong> 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..

Recommend

  • How do I sort a parent class based on an attribute of the child class?
  • XSLT - Selecting Min, Max, and Avg Attribute Values - Need Data from different Levels
  • sqlmap with error ValueError: _type_ 'v' not supported
  • Using a relational database and a key-value store in combination
  • Insert many records using ADO
  • Set max column value as sequence start value with liquibase tags
  • missing numbers script wont work? [duplicate]
  • Continuous data migration from mysql to Hbase
  • how important is setting max fd on select?
  • Join operation and restrictions at the same time
  • Joins and restrictions for different columns at the same time
  • Retrieving tables located on metadata server with SAS
  • SQL Count Grouping by a sequence of numbers
  • RxAndroidBle: Setup notification, write on characteristic and wait for notification to proceed
  • Convert 7 digit Julian Date to DateTime in SQL?
  • Oracle TOP N ordered rows
  • javascript regex replace, how to provide regex condition (and flags) as variable instead of inline?
  • Get date even if it doesn't exist in table from SQL SELECT statement
  • Excel - find nth match
  • T-SQL Pivot Add Total Row
  • What kind of data model models function parameters and results?
  • Show recently visited html pages by any visitor
  • Serializable transactions not protecting me from double inserts
  • How to distinguish field that requires null=True when blank=True is set in Django models?
  • What is the best data type to store boolean values in a database
  • Django Rest Framework: getting lists from query_params without request.getlist
  • Simple command-line app I/O in Dart
  • Is there a javascript serializer for JSON.Net?
  • Where to put my custom functions in Wordpress?
  • How to handle AllServersUnavailable Exception
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • Buffer size for converting unsigned long to string
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?
  • Django query for large number of relationships
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • Binding checkboxes to object values in AngularJs
  • How can I use `wmic` in a Windows PE script?
  • How to push additional view controllers onto NavigationController but keep the TabBar?