DateAdd and DateDiff function in Teradata??

Database
Enthusiast

DateAdd and DateDiff function in Teradata??

On SQL Server you can do something like DateAdd(ms, DateDiff(ms, date1, date2), date1)/2

To get the mid date between two date 2016-02-03 12:00:00 and 2016-02-03 14:00:00, you should get the result 2016-02-03 13:00:00.

How do you do that in Teradata when you have two timestamp(6) values?

Peter Schwennesen

3 REPLIES
Senior Apprentice

Re: DateAdd and DateDiff function in Teradata??

Hi Peter,

as long as the difference between the timestamps is less than 10000 days:

SELECT TIMESTAMP '2016-02-03 12:00:00' AS ts1, TIMESTAMP '2016-02-03 14:00:00' AS ts2,
ts1 + ((ts2-ts1 DAY(4) TO SECOND)/2)
Enthusiast

Re: DateAdd and DateDiff function in Teradata??

Thanks Dieter, I think I can keep it under 10000 days. why the 10000 day limit? is is because of the DAY(4) ??

br

Peter Schwennesen

Senior Apprentice

Re: DateAdd and DateDiff function in Teradata??

Hi Peter,

yep, it's that stupid 4-digit limit for intervals :)