sqloracledateinterval

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


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

Solution

  • 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