Search This Blog

Thursday 2 March 2017

How to calculate time difference between two dates?

--ORACLE

CREATE OR REPLACE FUNCTION DATETIMEDIFFCALC(FRMDATE DATE, TODATE DATE)
  RETURN VARCHAR2 AS
  V_OUT VARCHAR2(100);
  V_DAY  INTEGER;
  V_HR   INTEGER;
  V_MN   INTEGER;
BEGIN
  SELECT EXTRACT(DAY FROM DIFF),
         EXTRACT(HOUR FROM DIFF),
         EXTRACT(MINUTE FROM DIFF)
    INTO V_DAY, V_HR, V_MN
    FROM (SELECT (CAST(FRMDATE AS TIMESTAMP) - CAST(TODATE AS TIMESTAMP)) DIFF
            FROM DUAL);

  V_OUT := 'DAY : ' || V_DAY || ' HOUR : ' || V_HR || ' MIN : ' || V_MN;
  RETURN V_OUT;
END DATETIMEDIFFCALC;


--SQL


DECLARE @FROM_DATE DATETIME = '2017-03-01 12:12:12:122'
DECLARE @TO_DATE DATETIME = GETDATE()

SELECT CONCAT('DAY :',DATEDIFF(DAY,@FROM_DATE,@TO_DATE), ' HOUR :',DATEDIFF(HH,@FROM_DATE,@TO_DATE), ' MIN :',DATEDIFF(MM,@FROM_DATE,@TO_DATE))

No comments:

Post a Comment