Tuesday, July 15, 2014

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.

No comments:

Post a Comment