Skip Headers
Oracle® Database SQL Language Reference
11g Release 1 (11.1)

Part Number B28286-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

TO_DSINTERVAL

Syntax

Description of to_dsinterval.gif follows
Description of the illustration to_dsinterval.gif

sql_format::=

Description of sql_format.gif follows
Description of the illustration sql_format.gif

ds_iso_format::=

Description of ds_iso_format.gif follows
Description of the illustration ds_iso_format.gif

Purpose

TO_DSINTERVAL converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL DAY TO SECOND type.

TO_DSINTERVAL accepts argument in one of the two formats:

In the SQL format, days is an integer between 0 and 999999999, hours is an integer between 0 and 23, and minutes and seconds are integers between 0 and 59. frac_secs is the fractional part of seconds between .0 and .999999999. One or more blanks separate days from hours. Additional blanks are allowed between format elements.

In the ISO format, days, hours, minutes and seconds are integers between 0 and 999999999. frac_secs is the fractional part of seconds between .0 and .999999999. No blanks are allowed in the value.

Examples

The following example selects from the hr.employees table the employees who had worked for the company for at least 100 days on January 1, 1990:

SELECT employee_id, last_name FROM employees
   WHERE hire_date + TO_DSINTERVAL('100 00:00:00')
   <= DATE '1990-01-01'
   ORDER BY employee_id;

EMPLOYEE_ID LAST_NAME
----------- ---------------
        100 King
        101 Kochhar
        200 Whalen