1. date type used while date type subtraction only get days SQLgt; SELECTsysdate-(sysdate-1) FROMdual; SYSDATE-(SYSDATE-1 )--------------
1. date type used while date type subtraction only get days SQLgt; SELECT sysdate-(sysdate-1) FROM dual; SYSDATE-(SYSDATE-1 )--------------
I. date type usage
SQL> select systimestamp ts, systimestamp + 1 dt from dual;
TS DT
-------------------------------------------------------
-10 10.40.00.265000 AM + 08:00 2010-12-5 1
Solve the implicit conversion problem (retain the timestamp type)
SQL> select distinct imestamp ts, systimestamp + numtodsinterval (1, 'day') dt from dual;
TS DT
------------------------------------------------------------------------------------------
Am + AM
Iii. Use of the interval year to month type
Interval year to month can be replaced by the numtoyminterval or to_yminterval function.
SQL> selectNumtoyminterval (5, 'Year') + numtoyminterval (2, 'month ')From dual;
NUMTOYMINTERVAL (5, 'Year') + NUMT
---------------------------------------
+ 000000005-02
SQL> select numtoyminterval (5*12 + 2, 'month') from dual;
NUMTOYMINTERVAL (5*12 + 2, 'month'
---------------------------------------
+ 000000005-02
SQL> select to_yminterval ('5-2') from dual;
TO_YMINTERVAL ('5-2 ')
---------------------------------------
+ 000000005-02
SQL> select interval '5-2' year to month from dual;
INTERVAL '5-2' YEARTOMONTH
---------------------------------------
+ 05-02
Iv. Use of the interval day to second type
Interval day to second can be replaced with numtodsinterval or to_dsinterval
SQL> select numtodsinterval (10, 'day') + numtodsinterval (2, 'hour') +
2 numtodsinterval (3, 'minute ') + numtodsinterval (2.3312, 'second ')
3 from dual;
NUMTODSINTERVAL (10, 'day') + NUMT
---------------------------------------
+ 000000010 02:03:02. 331200000
SQL> select numtodsinterval (10*24*60*60*60 + 2*3600 + 3*60 + 2.3312, 'second') from dual;
NUMTODSINTERVAL (10*24*60*60 + 2 *
---------------------------------------
+ 000000010 02:03:02. 331200000
SQL> select to_dsinterval ('10 02:03:02. 3312 ') from dual;
TO_DSINTERVAL ('2017: 03: 661'
---------------------------------------
+ 000000010 02:03:02. 331200000
SQL> select interval '10 02:03:02. 3312 'Day to second from dual;
INTERVAL '2017: 03: 8080' DAYTO
---------------------------------------
+ 10 02:03:02. 331200
V. Time Functions
ADD_MONTHS Function
The ADD_MONTHS function adds the specified number of months to a date, so the day in the date will not change. However, if the start date is the last day of a month, the result will be adjusted
The returned value still corresponds to the last day of the new month. If the number of days in the result month is less than the number of days in the Start month, it is adjusted back to adapt to the valid date.
Select ADD_MONTHS (TO_DATE ('15-11-1961 ', 'dd-mm-yyyy'), 1) from dual; -- 1961-12-15
Select ADD_MONTHS (TO_DATE ('30-11-1961 ', 'dd-mm-yyyy'), 1) from dual; -- 1961-12-31
Select ADD_MONTHS (TO_DATE ('31-1-1999 ', 'dd-mm-yyyy'), 1) from dual; --
MONTHS_BETWEEN Function
MONTHS_BETWEEN (x, y) is used to calculate the number of months between x and y. If x is earlier than y in the calendar, MONTHS_BETWEEN () returns a negative number.
When the month difference between x and y is not the whole month, it can be expressed in decimal places.
NUMTODSINTERVAL () function
NUMTODSINTERVAL (x, interval_unit) is used TO convert the number x specified in interval_unit into an interval day to second type,
The interval_unit parameter can be set to DAY, HOUR, MINUTE, or SECOND.
NUMTOYMINTERVAL () function
NUMTOYMINTERVAL (x, interval_unit) is used TO convert the number x specified in interval_unit into an interval year to month type,
The interval_unit parameter can be set to YEAR or MONTH.