In Oracle Database, the TO_YMINTERVAL()
function converts its argument to a value of INTERVAL MONTH TO YEAR
data type.
Syntax
The syntax goes like this:
TO_YMINTERVAL
( ' { [+|-] years - months
| ym_iso_format
} '
[ DEFAULT return_value ON CONVERSION ERROR ]
)
You have the option of passing the argument in SQL format or ISO format.
Here’s the difference:
sql_format
is SQL interval format compatible with the SQL standard (ISO/IEC 9075).ds_iso_format
is ISO duration format compatible with the ISO 8601:2004 standard.
In SQL format, years
is an integer between 0 and 999999999, and months
is an integer between 0 and 11. Additional blanks are allowed between format elements.
In ISO format (syntax below), years and months are integers between 0 and 999999999. Days, hours
, minutes
, seconds
, and frac_secs
are non-negative integers, and are ignored, if specified. No blanks are allowed in the value. If you specify T
, then you must specify at least one of the hours
, minutes
, or seconds
values.
The syntax for ds_iso_format
goes like this:
[-] P [ years Y ] [months M] [days D]
[T [hours H] [minutes M] [seconds [. frac_secs] S ] ]
Examples
Here are some examples to demonstrate.
SQL Format
Here, I pass the argument in SQL format:
SELECT TO_YMINTERVAL('1-2')
FROM DUAL;
Result:
+01-02
In this case, I passed one year and two months in SQL format.
We can prepend it with its sign if necessary. Let’s flip it around to a negative:
SELECT TO_YMINTERVAL('-1-2')
FROM DUAL;
Result:
-01-02
ISO Format
In this example I pass the same value, but in ISO format:
SELECT TO_YMINTERVAL('P1Y2M')
FROM DUAL;
Result:
+01-02
Here it is with a negative value:
SELECT TO_YMINTERVAL('-P1Y2M')
FROM DUAL;
Result:
-01-02
Provide a Default Value on Conversion Error
You also have the option of providing a value to return in the event that there’s an error while converting the argument to an INTERVAL MONTH TO YEAR
type.
Example:
SELECT
TO_YMINTERVAL(
'1y-02m'
DEFAULT '00-00' ON CONVERSION ERROR
)
FROM DUAL;
Result:
+00-00
Null Arguments
Passing null
results in null
:
SET NULL 'null';
SELECT
TO_YMINTERVAL(null)
FROM DUAL;
Result:
null
By default, SQLcl and SQL*Plus return a blank space whenever null
occurs as a result of a SQL SELECT
statement.
However, you can use SET NULL
to specify a different string to be returned. Here I specified that the string null
should be returned.
Invalid Argument Count
Calling the function without passing any arguments, results in an error:
SELECT TO_YMINTERVAL()
FROM DUAL;
Result:
SQL Error: ORA-00938: not enough arguments for function 00938. 00000 - "not enough arguments for function
And passing too many arguments results in an error:
SELECT TO_YMINTERVAL('P1Y2M', 'P3Y5M')
FROM DUAL;
Result:
SQL Error: ORA-00939: too many arguments for function 00939. 00000 - "too many arguments for function"