Useful Oracle Functions

How to find the difference between two dates

If you want to find the difference between two dates according to different time types, you can use the EXTRACT function for this. Note that your data type must be timestamp.

SELECT  
    DATE1,
    DATE2,
    (DATE2-DATE1) DIFF,
    EXTRACT(HOUR FROM (DATE2-DATE1)) HOUR_DIFF,
    EXTRACT(MINUTE FROM (DATE2-DATE1)) MIN_DIFF,
    EXTRACT(SECOND FROM (DATE2-DATE1)) SEC_DIFF
FROM (
    SELECT 
        TO_TIMESTAMP('16/03/2020 13:00:00', 'dd/mm/yyyy hh24:mi:ss') DATE1, 
        TO_TIMESTAMP('16/03/2020 14:50:00', 'dd/mm/yyyy hh24:mi:ss') DATE2
    FROM DUAL
);
SELECT  
    (
        EXTRACT(HOUR FROM (DATE2-DATE1))) + 
        (EXTRACT(MINUTE FROM (DATE2-DATE1)) / 60 +
        (EXTRACT(SECOND FROM (DATE2-DATE1)) / (60 * 60))
    ) DIFF_IN_HOURS,
    (
        EXTRACT(HOUR FROM (DATE2-DATE1))) * 60 + 
        (EXTRACT(MINUTE FROM (DATE2-DATE1)) +
        (EXTRACT(SECOND FROM (DATE2-DATE1)) / 60)
    ) DIFF_IN_MINUTES,
    (
        EXTRACT(HOUR FROM (DATE2-DATE1))) * 60 * 60 + 
        (EXTRACT(MINUTE FROM (DATE2-DATE1))) * 60 + 
        (EXTRACT(SECOND FROM (DATE2-DATE1))
    ) DIFF_IN_SECONDS
FROM (
    SELECT 
        TO_TIMESTAMP('16/03/2020 13:00:00', 'dd/mm/yyyy hh24:mi:ss') DATE1, 
        TO_TIMESTAMP('16/03/2020 14:50:00', 'dd/mm/yyyy hh24:mi:ss') DATE2
    FROM DUAL
);

You can also get the last date, first date, previous month first and last dates, next month first and last dates of the corresponding date column.

SELECT 
    LAST_DAY(D1) LD, --Last date of the month
    TRUNC(D1, 'MM') FD, --First date of the month
    LAST_DAY(ADD_MONTHS(D1, -1)) LD_PM, --Last date of the previous month
    TRUNC(ADD_MONTHS(D1, -1), 'MM') FD_PM, --First date of the previous month
    LAST_DAY(ADD_MONTHS(D1, 1)) LF_NM, --Last date of the next month
    TRUNC(ADD_MONTHS(D1, 1), 'MM') FD_NM, --First date of the next month
    TRUNC(D1, 'YYYY') FDY, --First date of the year
    LAST_DAY(ADD_MONTHS(TRUNC(D1, 'YYYY'), 12)) LDY --Last date of the year
FROM (
    SELECT TO_DATE('19/03/2021') D1 FROM DUAL
);

How to add any time interval to date data

You can use INTERVAL expression for this.

SELECT 
    TRUNC(SYSDATE) C1,
    TRUNC(SYSDATE) + INTERVAL '1' HOUR C2, --adds 1 hour
    TRUNC(SYSDATE) + INTERVAL '1' MINUTE C3, --adds 1 minute
    TRUNC(SYSDATE) + INTERVAL '1' SECOND C4 --adds 1 second
FROM DUAL;
SELECT 
    TRUNC(SYSDATE) + INTERVAL '20:00:00' HOUR TO SECOND C1, --adds 20 hours, 0 min and 0 sec
    TRUNC(SYSDATE) + INTERVAL '1 20:10:05' DAY TO SECOND C2, --adds 1 day, 10 hours, 10 minutes and 5 seconds
    TRUNC(SYSDATE) + INTERVAL '1 20' DAY TO HOUR C3, --adds 1 day and 20 hours
    TRUNC(SYSDATE) + INTERVAL '1-1' YEAR TO MONTH C4 --adds 1 year and 1 month
FROM DUAL;

Data Type Conversion

From Timestamp to a specific TIME ZONE

AT TIME ZONE clause is one way to convert a timestamp to a specific timezone. You can apply it to a TIMESTAMP WITH TIME ZONE datatype and also a plain TIMESTAMP datatype. However, the conversion will use session timezone for TIMESTAMP datatype.

CREATE TABLE T1
(
    C1 TIMESTAMP,
    C2 TIMESTAMP WITH TIME ZONE
);

INSERT INTO T1(C1, C2) 
VALUES (
        TO_TIMESTAMP('16/03/2020 13:00:00', 'dd/mm/yyyy hh24:mi:ss'), 
        TO_TIMESTAMP_TZ('16/03/2020 13:00:00 America/Los_Angeles', 'dd/mm/yyyy hh24:mi:ss tzr')
       );

COMMIT;

SELECT
    C1, 
    C1 AT TIME ZONE 'UTC' AS C1_TZ,
    SYS_EXTRACT_UTC(C1) C1_UTC
FROM T1;
SELECT 
    C2, 
    C2 AT TIME ZONE 'Europe/Copenhagen' AS C2_TZ
FROM T1;

Other Conversions

With Oracle 12C release 2, a number for enhancements to data type conversion has come. You can use VALIDATE_CONVERSION function in order to check that whether the column data is eligeble for intended data conversion or not. The function returns value 1 if the conversion is successful, 0 if it fails.

CREATE TABLE T1
(
    C1 VARCHAR2(500)
);

INSERT INTO T1 VALUES('10');

INSERT INTO T1 VALUES('10/10/2021');

INSERT INTO T1 VALUES('Text Data');

COMMIT;
SELECT * FROM T1;
SELECT 
    TO_DATE(C1, 'DD/MM/YYYY') C1_DATE
FROM T1 
WHERE VALIDATE_CONVERSION(C1 AS DATE, 'DD/MM/YYYY') = 1;
SELECT 
    TO_NUMBER(C1) C1_NUMBER
FROM T1 
WHERE VALIDATE_CONVERSION(C1 AS NUMBER) = 1;

if you use Oracle 11g or earlier versions, you can use the following function.

CREATE FUNCTION IS_NUMBER(P_NUM IN VARCHAR2)
RETURN NUMBER
IS 
BEGIN

    RETURN CASE WHEN TO_NUMBER(P_NUM) IS NOT NULL THEN 1 END;

EXCEPTION 
    WHEN OTHERS THEN
    RETURN 0;
END;

SELECT 
    TO_NUMBER(C1) C1_NUMBER
FROM T1 
WHERE IS_NUMBER(C1) = 1;

Moreover, Oracle 12.2 and onwards some data type conversion functions contain error handling functionality.

SELECT 
    TO_NUMBER(C1 DEFAULT -1 ON CONVERSION ERROR) 
FROM T1;
SELECT 
    C1, 
    CAST(C1 AS TIMESTAMP DEFAULT NULL ON CONVERSION ERROR, 'DD/MM/YYYY HH24:MI:SS') CONVERTED_TO_TIMESTAMP
FROM T1;

COALESCE

Returns the first non-null parameter. If all given parameters are null then it returns null. All parameters should be the same data type. Otherwise, it gives an error. In addition, unlike NVL, short-circuit evaluation happen in COALESCE which means if the first matching parameter is found then other parameters are not executed.

--IF PARAM1 IS NOT NULL THEN
--    RETURN PARAM1
--ELSIF PARAM2 IS NOT NULL THEN
--    RETURN PARAM2
--ELSE 
--    RETURN NULL
--END IF;

--The following query returns 1
SELECT
    COALESCE(C1, C2, C3) RES
FROM
(
    SELECT 
        1 C1, 
        2 C2, 
        NULL C3 
    FROM DUAL
);

I hope this will help you.

Posted in SQL
Leave a Reply

Your email address will not be published. Required fields are marked *