Tag Archives: subtract month

Data function and format in oracle

Date function and format in Oracle

NUMTODSINTERVAL: Difference in dates with result in DAYS, HOURS, MINUTE and SECONDS

-- You can do it by callculaation
SELECT TRUNC( difference ) AS days,
TRUNC( MOD( difference * 24, 24 ) ) AS hours,
TRUNC( MOD( difference * 24*60, 60 ) ) AS minutes,
TRUNC( MOD( difference * 24*60*60, 60 ) ) AS seconds
FROM (
SELECT TO_DATE( '2018-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
- TO_DATE( '2018-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )
AS difference
FROM DUAL
);

DAYS HOURS MINUTES SECONDS
---- ----- ------- ----------
1    1     1       12

-- You can use numtodsinterval function to do for you.
SELECT EXTRACT( DAY FROM difference ) AS days,
EXTRACT( HOUR FROM difference ) AS hours,
EXTRACT( MINUTE FROM difference ) AS minutes,
EXTRACT( SECOND FROM difference ) AS seconds
FROM (
SELECT NUMTODSINTERVAL(
TO_DATE( '2018-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
- TO_DATE( '2017-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' ),
'DAY'
) AS difference
FROM DUAL
);

DAYS HOURS MINUTES SECONDS
---- ----- ------- --------
366  1     1       12

NUMTODSINTERVAL direct output

SELECT NUMTODSINTERVAL(
TO_DATE( '2018-01-02 01:01:12', 'YYYY-MM-DD HH24:MI:SS' )
- TO_DATE( '2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' ),
'DAY'
) AS difference
FROM DUAL;

DIFFERENCE
-------------------------------
+000000732 01:01:12.000000000

MONTH_BETWEEN function difference in Month
Note: Month_BETWEEN function calculated the month based of 31 days

SELECT MONTHS_BETWEEN( DATE '2018-03-10', DATE '2017-03-10' ) AS difference FROM DUAL;

DIFFERENCE
----------
12

Extract the Year, Month, Day, Hour, Minute

SELECT EXTRACT (YEAR FROM DATE '2018-03-25') AS YEAR,
EXTRACT (MONTH FROM DATE '2018-03-25') AS MONTH,
EXTRACT (DAY FROM DATE '2018-03-25') AS DAY
FROM DUAL;

YEAR MONTH DAY
---- ----- ----
2018 3     25

Format the Date with TO_CHAR Function as output

SELECT TO_CHAR( sysdate, 'YYYY-MM-DD' ) AS formatted_date FROM dual;
FORMATTED
----------
2018-09-10

SQL> SELECT TO_CHAR( sysdate, 'YYYY-DD-MM' ) AS formatted_date FROM dual;
FORMATTED
----------
2018-10-09

SELECT TO_CHAR( sysdate, 'DD-MON-YYYY' ) AS formatted_date FROM dual;
FORMATTED_DATE
--------------------
10-SEP-2018

SELECT TO_CHAR(sysdate,'FMMonth d yyyy, hh12:mi:ss AM','NLS_DATE_LANGUAGE = French') AS formatted_date FROM dual;
FORMATTED_DATE
----------------------------------
Septembre 2 2018, 2:49:44 PM

ADD_MONTHS function : you can subtract also by – sign
With add month function you can add more month to current date and by using – sign you can subtract also.

SQL> SELECT ADD_MONTHS(DATE'2015-01-12', 2) m FROM dual;
M
---------
12-MAR-15

SQL> SELECT ADD_MONTHS(DATE'2015-01-12', -2) m FROM dual;
M
---------
12-NOV-14

SQL> SELECT TO_CHAR( ADD_MONTHS(DATE'2015-01-31', 1),'YYYY-MM-DD') m FROM dual;
M
----------
2015-02-28

Advertisements