--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