calculate date error

Database
Enthusiast

calculate date error

I am trying to find out how many people purchased something in last 12, 18 or 24 months starting from 31st May and looking back. But keep getting invalid date error in terdata. What am I doing wrong. 

SELECT 

      CUST_ID

      ,CASE WHEN COALESCE(REGION,'XX') = 'US'  THEN 'US' 

                    WHEN COALESCE(REGION,'XX') = 'Eu'  THEN 'Europe' 

                    ELSE 'REST' 

        END AS SRC_REGION

         ,CAST(MAX(CHKIN_DT) AS DATE FORMAT 'YYYY-MM-DD') AS LAST_PRCHS_DT

        ,(DATE '2016-05-31'  - LAST_PRCHS_DT )AS RECENCY_DAYS        

        ,CASE WHEN LAST_PRCHS_DT BETWEEN 

                                                   DATE '2016-05-31'  AND (DATE '2016-05-31'  - INTERVAL '12' MONTH ) THEN '12_ACTIVE'

         WHEN LAST_PRCHS_DT BETWEEN 

                                                   DATE '2016-05-31'  AND (DATE '2016-05-31'  - INTERVAL '18' MONTH ) THEN '18_ACTIVE'

           WHEN LAST_PRCHS_DT BETWEEN 

                                                   DATE '2016-05-31'  AND (DATE '2016-05-31'  - INTERVAL '24' MONTH ) THEN '24_ACTIVE'

             ELSE 'OTHER'

             END AS ACTIVE_FLAG

FROM table_a

GROUP BY 1,2

3 REPLIES
Teradata Employee

Re: calculate date error

Don't use INTERVAL MONTH arithmetic for this. There is no 2014-11-31 (18 months before 2016-05-31), but ANSI semantics for INTERVAL types only modifies the portion of the date/time value at the precision of the interval or above (i.e. MONTH arithmetic only adjusts MONTH and YEAR, not DAY).

Use ADD_MONTHS(the_date, -18) instead and the function will adjust to the last day of the month.

Enthusiast

Re: calculate date error

That was a good point. Add_months is more intuitive for me. But the problem was smething more trivial. It seems that teradata does not like bigger date to be placed earlier in a BETWEEN statement when specifying 2 dates. Thanks for your help!

Junior Contributor

Re: calculate date error

Well, this is not Teradata related, in fact no database likes that :)

When you Explain your query you'll see "unsatisfiable conditions".