PL/SQL Package for date calculation and format conversion

PL/SQL Package: Exercise-6 with Solution

Write a PL/SQL package that includes procedures and functions to handle date and time operations, such as calculating the difference between two dates and converting between different date formats.

Sample Solution:

PL/SQL Code:

CREATE OR REPLACE PACKAGE DateTimePackage AS
  FUNCTION CalculateDateDifference(start_date IN DATE, end_date IN DATE) RETURN NUMBER;
  FUNCTION ConvertDateFormat(date_value IN DATE, format IN VARCHAR2) RETURN VARCHAR2;
END DateTimePackage;
/
CREATE OR REPLACE PACKAGE BODY DateTimePackage AS
  FUNCTION CalculateDateDifference(start_date IN DATE, end_date IN DATE) RETURN NUMBER IS
diff NUMBER;
  BEGIN
diff := end_date - start_date;
    RETURN diff;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20001, 'An error occurred while calculating date difference.');
  END CalculateDateDifference;
  FUNCTION ConvertDateFormat(date_value IN DATE, format IN VARCHAR2) RETURN VARCHAR2 IS
converted_dateVARCHAR2(100);
  BEGIN
converted_date := TO_CHAR(date_value, format);
    RETURN converted_date;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20002, 'An error occurred while converting date format.');
  END ConvertDateFormat;
END DateTimePackage;
/

Sample Output:

Package created.
Package Body created.

Flowchart:

To execute the package:

DECLARE
start_date DATE := TO_DATE('2022-01-01', 'YYYY-MM-DD');
end_date DATE := SYSDATE;
date_diff NUMBER;
converted_dateVARCHAR2(100);
BEGIN
date_diff := DateTimePackage.CalculateDateDifference(start_date, end_date);
  DBMS_OUTPUT.PUT_LINE('Date Difference in Days: ' || date_diff);
converted_date := DateTimePackage.ConvertDateFormat(end_date, 'DD-MON-YYYY HH24:MI:SS');
  DBMS_OUTPUT.PUT_LINE('Converted Date: ' || converted_date);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Sample Output:

Statement processed.
Date Difference in Days: 560.337395833333333333333333333333333333
Converted Date: 15-JUL-2023 08:05:51

Flowchart:

Explanation:

The said code in Oracle's PL/SQL package that offers functionality for date calculation and format conversion.

The package consists of two main components the CalculateDateDifference function and the ConvertDateFormat function.

The CalculateDateDifference returns a number representing the number of days that separate two dates by subtracting the start_date from the end_date and any errors occurring during the calculation exception will be handled and an appropriate error message will be displayed.

The ConvertDateFormat function converts a given date_value to a specified format using the TO_CHAR function and returns the converted date as a VARCHAR2 string.

Similarly, the package handles exceptions and throws an error if any issues arise during the format conversion process.

Previous: PL/SQL package for password generation and strength checking.
Next: Calculate_avg_salary.