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.
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.
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!