How to subtract dates in Teradata to get a year, month, or a day

We can subtract dates to get the years, months, days in Teradata. Developers also search for datediff in teradata. datediff function is available in SQL server but not in the Teradata. But Teradata provides a special type of syntax to take the difference of dates.

Let's see how to use this special type of syntax to take the difference of two dates in Teradata.

 SELECT (DATE 'YYYY-MM-DD' - DATE 'YYYY-MM-DD') UNIT;

In this special format, the extraction of higher unit value ignores the smaller unit values. For example, if we subtract two dates to get the month value (higher unit value) then the day values in the DATE values are ignored.

1. Subtract DATE values to get years

The following query subtracts two date values and gives us the number of years.

 SELECT (DATE '2019-07-10' - DATE '2018-01-28') YEAR;

The result of the above-given query is as follows.

 (2019-07-10 - 2018-01-28) YEAR
 ------------------------------
 1

When calculating the difference in years between DATE values, the smaller unit values such as months are ignored. For example, there is a difference of 5 months but teradata ignores months and finds the difference of year values only.

2. Subtract DATE values to get months

The following query finds the difference between two DATE values and gives us the number of months.

 SELECT (DATE '2018-04-10' - DATE '2019-01-28') MONTH;

The result of the above-given query is as follows.

 (2019-04-10 - 2018-10-28) MONTH
 ------------------------------
 6

In the above-given example, the difference of dates gives us the number of months. In this case, the teradata ignores the numbers of days. So the total number of months between two dates is 6.

3. Subtract DATE values to get days

The following query finds the difference between two DATE values and gives us the number of days.

 SELECT (DATE '2019-01-10' - DATE '2018-12-28') DAY;

The result of the above-given query is as follows.

 (2019-01-10 - 2018-12-28) DAY
 ------------------------------
 12

The difference between two date values is 12.

Summary

We can subtract two DATE values to get the required number of years, months, or days. So there is no such kind of term as datediff in Teradata. But there is different type of syntax available in Teradata to subtract two values. And It is important to remember that whenever we take the difference of DATE values for some unit then the smaller unit values are ignored.


Was this article helpful?

 

Email:

Message: