How to compare INTERVAL YEAR TO MONTH data types in Oracle SQL Developer?

Question

so I have table Product_Information

Product	Data type
Product_ID	NUMBER(6,0)
Warranty_Period	INTERVAL YEAR(2) TO MONTH

How should I do a diapason check between intervals? This is the code I've tried

case W.WARRANTY_PERIOD 
    when INTERVAL '0-0' YEAR TO MONTH THEN 'No warranty' 
    when INTERVAL '0-3' YEAR TO MONTH THEN '3 months' 
    end as Warranty 

I've already joined the table, but I need result like this:

ProductID	Warranty Left
102	1 year and 6 months
103	4 months

Answer 1

Use EXTRACT in a CASE expression:

SELECT product_id,
       CASE EXTRACT(YEAR FROM warranty_period)
       WHEN 0 THEN NULL
       WHEN 1 THEN '1 year'
       ELSE EXTRACT(YEAR FROM warranty_period) || ' years'
       END
       || CASE
       WHEN EXTRACT(YEAR FROM warranty_period) > 0
       AND  EXTRACT(MONTH FROM warranty_period) > 0
       THEN ' and '
       WHEN EXTRACT(YEAR FROM warranty_period) = 0
       AND  EXTRACT(MONTH FROM warranty_period) = 0
       THEN 'No warranty'
       END
       || CASE EXTRACT(MONTH FROM warranty_period)
       WHEN 0 THEN NULL
       WHEN 1 THEN '1 month'
       ELSE EXTRACT(MONTH FROM warranty_period) || ' months'
       END AS warranty
FROM   product_information;

Which, for the sample data:

CREATE TABLE product_information (
  Product_ID      NUMBER(6,0),
  Warranty_Period INTERVAL YEAR(2) TO MONTH
);

INSERT INTO product_information (product_id, warranty_period)
SELECT 102, INTERVAL '1-6' YEAR TO MONTH FROM DUAL UNION ALL
SELECT 103, INTERVAL '4' MONTH FROM DUAL UNION ALL
SELECT 104, INTERVAL '0' MONTH FROM DUAL UNION ALL
SELECT 105, INTERVAL '1' MONTH FROM DUAL UNION ALL
SELECT 106, INTERVAL '1' YEAR FROM DUAL;

Outputs:

PRODUCT_ID	WARRANTY
102	1 year and 6 months
103	4 months
104	No warranty
105	1 month
106	1 year

db<>fiddle here

Answer 2

You can use extract function to extract parts (year, month) of your interval period like below

select PRODUCT_ID, WARRANTY_PERIOD
, case W.WARRANTY_PERIOD 
    when INTERVAL '0-0' YEAR TO MONTH THEN 'No warranty'
    else 
      case 
        when extract (year from W.WARRANTY_PERIOD) = 1 then '1 year'
        when extract (year from W.WARRANTY_PERIOD) > 1 then extract (year from W.WARRANTY_PERIOD) ||' years'
        else null
      end
  ||  case 
        when extract (year from W.WARRANTY_PERIOD) > 0 
          then
            case 
              when extract (month from W.WARRANTY_PERIOD) = 1 then ' and 1 month'
              when extract (month from W.WARRANTY_PERIOD) > 1 then ' and ' || extract (month from W.WARRANTY_PERIOD) || ' months'
              else null
            end
        else 
          case 
              when extract (month from W.WARRANTY_PERIOD) = 1 then '1 month'
              when extract (month from W.WARRANTY_PERIOD) > 1 then extract (month from W.WARRANTY_PERIOD) || ' months'
              else null
          end
        end
  end as Warranty 
from Product_Information w
;

Answer 3

You can use a regular expression along with TO_NUMBER() conversion such as

WITH t2 AS
(
 SELECT Warranty_Period,
        TO_NUMBER(REGEXP_SUBSTR( Warranty_Period, '[^-]+' )) AS Warranty_y,
        TO_NUMBER(REGEXP_SUBSTR( Warranty_Period, '[^-]+$')) AS Warranty_m 
   FROM t       
)
SELECT CASE Warranty_Period
         WHEN INTERVAL '0-0' YEAR TO MONTH THEN
          'No warranty'
         ELSE
          DECODE(SIGN(Warranty_y),1,NVL2(Warranty_y,Warranty_y||' year ',''))||  
          CASE WHEN Warranty_y*Warranty_m > 0 THEN 'and ' END||        
          DECODE(SIGN(Warranty_m),1,Warranty_m||' month')||
          CASE WHEN Warranty_m > 1 THEN 's' END
       END AS Warranty
  FROM t2

Demo