- CAST with Interval data types:
As mentioned earlier we can cast any of the YEAR-MONTH interval type to any other
YEAR-MONTH interval type
We can also make mathematical operations such as division and
multiplication on the interval types.
Example:
SELECT CAST( (INTERVAL '3-06' YEAR TO MONTH) AS INTERVAL MONTH)
;
3-06
42
SELECT CAST ((INTERVAL '3-02' YEAR TO MONTH) AS INTERVAL YEAR);
3
SELECT INTERVAL '3-02' YEAR TO MONTH / 6;
0-06
SELECT INTERVAL '8' YEAR / 3;
2
Note the following query .
Select CAST ( ( DATE
'2013-07-07' - DATE '2013-04-04') AS INTERVAL MONTH);
(2013-07-07-2013-04-04)
1
94
Note the result is shown as 94, which seems wrong because we
wanted to have date difference result as months . However the result is the
actual number of days. The reason is way the query worked. We first subtracted
the dates and the result is 94 . As we know result of date subtraction is
integer , the integer 94 gets casted to data type month, which changes just the
data type and not the value. Thus we get the result which is nothing by integer
casted to date value.
The above query is equivalent to the one given below
SELECT CAST ( (95-1) AS INTERVAL MONTH);
94
We can get the result we want by using the implicit cast as show
below:
Select ( DATE
'2013-07-07' - DATE '2013-04-04') MONTH;
3
I don’t know whats the difference between impicit and explicit
cast.
Select CAST ( ( DATE
'2013-07-07' - DATE '2013-04-04') AS INTERVAL YEAR TO MONTH);
This query will fail because result of minus will be a integer
which is not of the format yy-mm and hence will cause the query to fail because
of invalid operation on Datetime or interval.
