CODEWITHSUNDEEP
sqlfunctiondateteradataintervals
senth54
senth54

Reputation: 11

Teradata Interval function

Teradata fails while using in-built function, INTERVAL, when used with MONTH specification for deriving dates on February month 

SELECT Cast('2017-12-29' as date) - INTERVAL '10' MONTH;
SELECT Cast('2017-12-30' as date) - INTERVAL '10' MONTH;
SELECT Cast('2017-12-31' as date) - INTERVAL '10' MONTH;
SELECT Cast('2018-12-29' as date) - INTERVAL '10' MONTH;
SELECT Cast('2018-12-30' as date) - INTERVAL '10' MONTH;
SELECT Cast('2018-12-31' as date) - INTERVAL '10' MONTH;
SELECT Cast('2019-12-29' as date) - INTERVAL '10' MONTH;
SELECT Cast('2019-12-30' as date) - INTERVAL '10' MONTH;
SELECT Cast('2019-12-31' as date) - INTERVAL '10' MONTH;
SELECT Cast('2020-12-30' as date) - INTERVAL '10' MONTH;
SELECT Cast('2020-12-31' as date) - INTERVAL '10' MONTH;

or

SELECT CURRENT_DATE - INTERVAL '10' MONTH;-- << If current date is 29,30,31 day of December month Non leap year and 30,31 day of December month leap year>>

Upvotes: 0

Views: 1699

Answers (1)

senth54
senth54

Reputation: 11

Use Add_Months function instead of Interval function..

SELECT ADD_MONTHS(CAST ('2017-12-29' AS DATE),-10);
SELECT ADD_MONTHS(CURRENT_DATE,-10); -- << If current date is 29,30,31 day of December month Non leap year and 30,31 day of December month leap year>>

Upvotes: 1

Related Questions