How to add months or days to date in Teradata using Add_months and Interval function
Contents
ADD_MONTHS in Teradata
Add_Months is a function that used to add or subtract months to date in Teradata. The output of this function may be future date (addition) or past date (subtraction).
Syntax of Add_Months
|
1 |
SELECT ADD_MONTHS(<date_column/date expression>, <number of months>) ; |
Add months function takes two parameter, one is date column or the exact date.The second parameter is the number as how many months you want to add to that date. If you specify the negative number, it will subtract the months from the date.
Example 1 : Add 3 months to current date
The current date is ‘2019-08-01’ (yyyy-mm-dd). Lets add 3 months to current date. The result will be 2019-11-01 as below.
|
1 |
SELECT ADD_MONTHS(CURRENT_DATE,3); |
Output:
Example 2 :Subtract 2 months using Add_months function
Add months is intelligently identify the leap year and return the correct date. Lets subtract the 2 months from the date ‘2019-04-30’. Since the February doesn’t have the 30 th day, it will return the ‘2019-02-28’ as below.
|
1 |
SELECT ADD_MONTHS('2019-04-30',-2); |
Output
Interval function in Teradata
Interval function is used to add or subtract YEAR, MONTH, DAY, HOUR, MINUTE and Seconds on DATE and TIME values. The interval qualifiers are listed below
Year-Month intervals:
- YEAR
- YEAR TO MONTH
- MONTH
Day-Time intervals:
- DAY
- DAY TO HOUR, MINUTE or SECOND
- HOUR
- HOUR TO MINUTE or SECOND
- MINUTE
- MINUTE to SECOND
- SECOND
Example 1 : ADD Years in Teradata
|
1 |
SELECT DATE '2019-08-02' + INTERVAL '1' YEAR; |
Example 2: Interval Year to Month in Teradata
|
1 2 3 |
CREATE TABLE Test_DB.Timedetails (Id INTEGER ,Offset INTERVAL YEAR (4) TO MONTH); |
Lets insert the values as ‘2019-04’ (Year(4) to month) to the offset column in Timedetails table.
|
1 |
insert into Test_DB.Timedetails values (1,'2019-04'); |
Subtract months in Teradata
We can subtract the months from the offset value using month qualifier in Interval function.
|
1 |
select (offset) - interval '3' month from Test_DB.Timedetails; |
ADD months in Teradata
|
1 |
SELECT CAST('2019-08-01' as DATE) + INTERVAL '3' month |
Example 3 : ADD Days in Teradata
|
1 |
SELECT DATE '2019-08-02' + INTERVAL '15' DAY; |
Since we specified 15 day as interval, it will add the 15 days to the date and returned the new date as below.
Issues on the Interval function
|
1 |
SELECT CAST('2019-07-31' as DATE) - INTERVAL '1' month |
The above query needs to return the date as ‘2019-06-30’ as we are subtracting 1 month from the date ‘2019-07-31’. But the interval function returns the error as “Invalid date“.
Since this function tries to return the same 31st day from the June month, it is throwing the error. In this situation , we can use ADD months to get the correct date as “2019-06-30′ as below.
|
1 |
SELECT ADD_MONTHS(CAST('2019-07-31' as DATE),-1) |
Recommended Articles
- Extract function in Teradata with examples
- Timestamp in Teradata with examples
- How to Cast the String to Date in Teradata?
- Timestamp to Date conversion in Teradata
