What is Dateadd Function Alternative in Teradata SQL?

In ANSI SQL, Dateadd function adds the specified value for the specified date or time part to a date, time, or timestamp. However, the same function is not available in Teradata. In this article, we will check what is Dateadd function alternative in Teradata SQL, methods and examples.

Dateadd Function Alternative in Teradata SQL

Many relational databases and data warehouse appliances such as Snowflake, SQL Server, Netezza, Oracle, etc support date add function to add or subtract days, months, years, hours, minutes, and seconds to date or timestamp value.

For example, consider below dateadd function in Snowflake.

SELECT DATEADD(month, 1, '2020-01-01');
+---------------------------------+
| DATEADD(MONTH, 1, '2020-01-01') |
|---------------------------------|
| 2020-02-01 00:00:00.000         |
+---------------------------------+

However, dateadd function is not available in Teradata. Instead, you can use other built-in functions such as add_months, add_days, interval type.

You can use following Teradata function as an alternative to dateadd function.

Interval Data Types
ADD_MONTHS in Teradata

Now, let us check these alternative methods in brief.

Teradata Interval Data Type as Dateadd Alternative

The INTERVAL data types in Teradata are handy and widely used types when working with date, time variables.

You can express interval types as a combination of the INTERVAL keyword with a numeric quantity and a supported date part such as year, month, days, etc.

For example: INTERVAL ‘1 days’ or INTERVAL ’10 minutes’.

Add Date Part to Date in Teradata using Interval Type

Following are the examples to add date part to date or timestamp value.

Add Year to Date using Interval Data Type

select cast ('2020-01-01 01:01:01' as timestamp) + interval '1' year;

Teradata Add Months to Date using Interval Data Type

select cast ('2020-01-01 01:01:01' as timestamp) + interval '1' month;

Add Days to Date using Interval Data Type

select cast ('2020-01-01 01:01:01' as timestamp) + interval '1' day;

Teradata Add Hours to Date using Interval Data Type

select cast ('2020-01-01 01:01:01' as timestamp) + interval '1' hour;

Add Minutes to Date using Interval Data Type

select cast ('2020-01-01 01:01:01' as timestamp) + interval '1' minute;

Add Seconds to Date using Interval Data Type

select cast ('2020-01-01 01:01:01' as timestamp) + interval '1' second;

If you have only months to add, you can use built-in Teradata date function such as add_months.

Add Months to Date in Teradata using add_months Function

As mentioned, you can use add_months function if you have a requirement to add only month to date or timestamp value.

For example,

Consider following Teradata SQL example to add months to timestamp.

select add_months(cast ('2020-01-01 01:01:01' as timestamp),1);