Below re list of function.
| Function | Description |
|---|---|
| LAST_DAY | Return date of the last day of the month that contains timestamp value |
| NEXT_DAY | returns first weekday named by day_value that is later than the date specified by date/timestamp value |
| NUMTODSINTERVAL | convert a numeric value into an INTERVAL DAY(4) TO SECOND(6) value |
| NUMTOYMINTERVAL | Convert a numeric value into an INTERVAL YEAR(4) TO MONTH value |
| TO_DSINTERVAL | Convert a string value into an INTERVAL DAY(4) TO SECOND(6) value. |
| TO_YMINTERVAL | Convert a string value into an INTERVAL YEAR(4) TO MONTH value. |
| MONTHS_BETWEEN | Return the number of months between two date/timestamp values. |
| OADD_MONTHS | Add a specified date/timestamp value to a specified number of months and return the resulting date. |
| TO_DATE | Convert a string into a DATE value via a format string. |
| TO_TIMESTAMP | Convert a string into a TIMESTAMP value via a format string. |
| TO_TIMESTAMP_TZ | Convert a string into a TIMESTAMP WITH TIME ZONE value via a format string. |
| TRUNC | Returns a DATE value with the time portion truncated to the unit specified by a format string. |
| ROUND | Returns a DATE value with the time portion rounded to the unit specified by a format string. |
Examples:
SELECT LAST_DAY (DATE);
-----------------------
LAST_DAY(Date)
2014-06-30
SELECT NEXT_DAY(DATE '2014-06-10' , 'FRIDAY');
----------------------------------------------
NEXT_DAY(2014-06-10,'FRIDAY')
2014-06-13
SELECT NUMTODSINTERVAL(86405,'SECOND'), NUMTOYMINTERVAL(100, 'MONTH' );
----------------------------------------------------------------------------------------------------------------------
NUMTODSINTERVAL(86405,'SECOND') NUMTOYMINTERVAL(100,'MONTH')
1 00:00:05.000000 8-04
SELECT TO_DSINTERVAL('150 08:30:00') , TO_YMINTERVAL( '2-11') ;
----------------------------------------------------------------------------------------------
TO_DSINTERVAL('150 08:30:00') TO_YMINTERVAL('2-11')
150 08:30:00.000000 2-11
The above functions can be helpful while adding to any date columns.
samples
SELECT ORDER_DATE, ORDER_DATE + TO_YMINTERVAL('02-11') FROM ORDERS;
SELECT EMP_ID, LAST_NAME FROM EMP_TABLE WHERE HIRE_DATE + TO_DSINTERVAL('100 00:00:00')
SELECT MONTHS_BETWEEN(DATE'2014-06-01', DATE'2014-02-01');
-------------------------------------------------------------
MONTHS_BETWEEN(2014-06-01,2014-02-01)
4.00
SELECT OADD_MONTHS (DATE '2014-04-15', 2), OADD_MONTHS (DATE '2008-02-29', 1);
---------------------------------------------------------------------------------------------------------------------
OADD_MONTHS(2014-04-15,2) OADD_MONTHS(2008-02-29,1)
2014-06-15 2008-03-31
Since 29 is the last day in February, March 31 is returned since 31 is the last day in March
SELECT TRUNC(CAST('2014/06/05' AS DATE), 'D') (FORMAT 'yyyy-mm-dd');
----------------------------------------------------------------------------------------------------
TRUNC('2014/06/05','D')
2014-06-01
The date was rounded to the first day of that week.
SELECT ROUND(CAST('2003/09/20' AS DATE), 'RM') (FORMAT 'yyyy-mm-dd');
----------------------------------------------------------------------------------------------------------
ROUND('2003/09/20','RM')
2003-10-01
Since the day is greater than or equal to 16, the date is rounded to the beginning of the next month.
