INTERVAL conversion functions

There are 4 convertions functions that deal with INTERVAL datatypes, as listed below.
The two first converts strings, the later two converts numbers. All functions returns data of datatype INTERVAL.

TO_DSINTERVAL(sql_format)

* Converts a string into a value of the INTERVAL DAY TO SECOND datatype
* sql_format is a character string in the required format, which is 'DAYS HH24:MI:SS.FF'
* For example, the string '2 8:30:15.444' would equal 2 days, 8 hours, 30 minutes, 15.444 seconds.

Output: A value of the INTERVAL DAY TO SECOND datatype

Example:

SELECT TO_DSINTERVAL(INTERVAL '4' HOUR) "HOUR"
FROM DUAL;

HOUR
+00 04:00:00.000000

TO_YMINTERVAL('y-m')
* Converts y and m into the years and months in a format recognized by the INTERVAL YEAR TO MONTH datatype
* y and m are numbers within a string

Output: A value of the INTERVAL YEAR TO MONTH datatype

Example:
SELECT TO_YMINTERVAL(INTERVAL '36' MONTH) "MONTH"
FROM DUAL;

MONTH
+03-00

NUMTOYMINTERVAL(n, u)

* Converts a number into a value that represents the u unit amount of time
n = number
u = 'YEAR' or 'MONTH'
* Output: a value of the INTERVAL YEAR TO MONTH datatype

Example:

SELECT NUMTOYMINTERVAL(4, 'MONTH') "MONTH"
FROM DUAL;

MONTH
+00-04

NUMTODSINTERVAL(n, u)
Converts a number into a value that represents the u unit amount of time
n = number
u = 'DAY', 'HOUR', 'MINUTE', 'SECOND'
Output: a value of the INTERVAL DAY TO SECOND datatype

Example:

SELECT NUMTODSINTERVAL(2,'MINUTE') "MINUTE"
FROM DUAL;

MINUTE
+00 00:02:00.000000

More on the TO_DSINTERVAL conversion function

The TO_DSINTERVAL function is very flexible.
All you need to do is to specify the interval you're looking for, and it will convert your string into a valid INTERVAL datatype. Intervals are DAY, HOUR, MINUTE, SECOND

Examples:

Turn 2 days into a valid INTERVAL datatype

SELECT TO_DSINTERVAL(INTERVAL '2' DAY)
 FROM DUAL;

 TO_DSINTERVAL(INTERVAL'2'DAY)
 --------------------------------------------------
 +02 00:00:00.000000
 1 row selected.

Turn 75 minutes into a valid INTERVAL datatype

SELECT TO_DSINTERVAL(INTERVAL '75' MINUTE)
 FROM DUAL;

 TO_DSINTERVAL(INTERVAL'75'MINUTE)
 --------------------------------------------------
 +00 01:15:00.000000
 1 row selected.

Turn 60 minutes into a valid INTERVAL datatype

SELECT TO_DSINTERVAL(INTERVAL '60' MINUTE)
 FROM DUAL;

 TO_DSINTERVAL(INTERVAL'60'MINUTE)
 --------------------------------------------------
 +00 01:00:00.000000
 1 row selected.

Turn 60 seconds into a valid INTERVAL datatype

SELECT TO_DSINTERVAL(INTERVAL '60' SECOND)
 FROM DUAL;

 TO_DSINTERVAL(INTERVAL'60'SECOND)
 --------------------------------------------------
 +00 00:01:00.000000
 1 row selected.

Turn 24 hours into a valid INTERVAL datatype

SELECT TO_DSINTERVAL(INTERVAL '24' HOUR)
 FROM DUAL;

 TO_DSINTERVAL(INTERVAL'24'HOUR)
 --------------------------------------------------
 +01 00:00:00.000000
 1 row selected.

Turn 6 hours into a valid INTERVAL datatype

SELECT TO_DSINTERVAL(INTERVAL '6' HOUR)
 FROM DUAL;

 TO_DSINTERVAL(INTERVAL'6'HOUR)
 --------------------------------------------------
 +00 06:00:00.000000
 1 row selected.

Turn 61.2 seconds into a valid INTERVAL datatype

SELECT TO_DSINTERVAL(INTERVAL '61.2' SECOND)
 FROM DUAL;

 TO_DSINTERVAL(INTERVAL'61.2'SECOND)
 --------------------------------------------------
 +00 00:01:01.200000
 1 row selected.

Turn 1 hour into a valid INTERVAL datatype

SELECT TO_DSINTERVAL(INTERVAL '1' HOUR)
 FROM DUAL;

 TO_DSINTERVAL(INTERVAL'1'HOUR)
 --------------------------------------------------
 +00 01:00:00.000000
 1 row selected.

How to use the different options for DATE datatype in Oracle

Datatypes that support dates and times come in two flavours:

Datetime data types, which are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE.

Interval data types, which are INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. Interval data types store time durations.

First, create a test table where the different types of DATE datatypes are bing used:

CREATE TABLE DATETYPETEST
    (TESTDATE1 DATE,
     TESTDATE2 TIMESTAMP,
     TESTDATE3 TIMESTAMP WITH TIME ZONE,
     TESTDATE4 TIMESTAMP WITH LOCAL TIME ZONE,
     TESTDATE5 INTERVAL YEAR TO MONTH,
     TESTDATE6 INTERVAL YEAR(4) TO MONTH,
     TESTDATE7 INTERVAL DAY TO SECOND,
     TESTDATE8 INTERVAL DAY TO SECOND(2)
     );

First I will test the Datetime data types:

INSERT INTO DATETYPETEST(TESTDATE1) VALUES(SYSDATE);
INSERT INTO DATETYPETEST(TESTDATE1) VALUES(TRUNC(SYSDATE));

TRUNC(SYSDATE)
17.01.2014 10:15:45
17.01.2014

INSERT INTO DATETYPETEST (TESTDATE2) VALUES (SYSTIMESTAMP);
INSERT INTO DATETYPETEST (TESTDATE2) VALUES (TRUNC(SYSTIMESTAMP));

TRUNC(SYSTIMESTAMP)
17.01.2014 10:15:45.978004
17.01.2014 00:00:00.000000

Interestingly enough, TRUNC on datatype TIMESTAMP does not strip of the time precision per se, Oracle simply sets it to zero altogether.

INSERT INTO DATETYPETEST (TESTDATE3) VALUES (SYSDATE);

TIMESTAMP WITH TIMEZONE
17.01.2014 10:15:46.000000 +01:00

INSERT INTO DATETYPETEST (TESTDATE4) VALUES (SYSDATE);

TIMESTAMP WITH LOCAL TIMEZONE
17.01.2014 10:15:46.000000

Remember that TIMESTAMP WITH LOCAL TIME ZONE does not store the time zone offset with the column value; however, when the column value is retrieved in a query, the value is sent in the user's local session time zone.

Let's move on to the interval data types.

First, I will insert the interval as a literal:

INSERT INTO DATETYPETEST (TESTDATE5) VALUES (INTERVAL '2-11' YEAR TO MONTH);

INTERVAL YEAR TO MONTH
+2-11

Below, I am using the TO_YMINTERVAL function to convert a string representing a number of years and a number of months into a INTERVAL YEAR TO MONTH datatype:

INSERT INTO DATETYPETEST (TESTDATE5) VALUES(TO_YMINTERVAL('14-01'));

INTERVAL YEAR TO MONTH
+14-01

The statement below, however, fails with ORA-01873: the leading precision of the interval is too small because used the default precision for year(2):

SQL> INSERT INTO DATETYPETEST (TESTDATE5) VALUES(TO_YMINTERVAL('2014-01'));

INSERT INTO DATETYPETEST (TESTDATE5) VALUES(TO_YMINTERVAL('2014-01'))
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small

So instead, let's insert the values into a column which allows for larger precision for the INTERVAL YEAR(n) TO MONTH datatype:

SQL> INSERT INTO DATETYPETEST (TESTDATE6) VALUES(TO_YMINTERVAL('2014-01'));

1 row created.

Of course, even values of less precision would be accepted here:

SQL> INSERT INTO DATETYPETEST (TESTDATE6) VALUES(TO_YMINTERVAL('14-01'));

1 row created.

INTERVAL YEAR(4) TO MONTH
+2014-01
+14-01

Again, I will first insert the interval as a literal:

INSERT INTO DATETYPETEST (TESTDATE7) VALUES(INTERVAL '2 6:30:01.222' DAY TO SECOND(3) ));

INTERVAL DAY TO SECOND
+02 6:30:01.222

Use the TO_DSINTERVAL function to convert the string representing a number of days, hours, minutes and seconds into an INTERVAL DAY TO SECOND datatype:

INSERT INTO DATETYPETEST (TESTDATE7) VALUES(TO_DSINTERVAL('7 12:30:000001'));

INTERVAL DAY TO SECOND
+07 12:30:01.000000

In the next example, I am limiting the number of fractional seconds according to the specifications of the column INTERVAL DAY TO SECOND(2):

INSERT INTO DATETYPETEST (TESTDATE8) VALUES(TO_DSINTERVAL('21 12:30:000001'));

INTERVAL DAY TO SECOND(2)
+21 12:30:01.00

The TO_DSINTERVAL is very flexible. Here is another post on the same topic.

An overview of conversion functions used with the INTERVAL data types can be found here

Sources: Oracle Documentation on intervals