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.