dateiff / dateadd function


dateiff / dateadd function

hi :)


someone know how convert to teradata:

datediff(day , a.UpdateDateTime , getdate()) >= b.MinDaysInOffer






Re: dateiff / dateadd function

Similar to your last question:

Current_Date - Cast(a.UpdateDateTime AS DATE) >= b.MinDaysInOffer

Re: dateiff / dateadd function

ok thanks!

can you explain how do you know that the output set on 'day'?


Re: dateiff / dateadd function

Sorry, I don't understand your question, waht do you mean by output set on 'day'?

Re: dateiff / dateadd function

tell me if i'm wrong: dateiff function subtracting between 2 parameters of date by : day/month/year...

where you write that in your code?


Re: dateiff / dateadd function

Now I got it :)


The day version is simple, because the difference between two dates returns the number of days as integer: date1 - date2

Similar for adding/subtracting days: date1 + 4


Regarding timestamps it's more difficult, Teradata implemented Standard SQL syntax, but introduced some stupid limits :(


The difference between two Timestamps is an INTERVAL:

Timestamp1- Timestamp2 YEAR (similar result to DATEDIFF(year))


The default number of digits based on Standard SQL is two (up to 99 years), but it can be changed using YEAR(n), but n is limited to 4 in Teradata:

Timestamp1- Timestamp2 YEAR(4)


This is ok for years, because Teradata covers a date range 0001-01-01 to 9999-12-31, but fails soon when you calculate months/days/hours/minutes/seconds.


You can calculate complex intervals like YEAR TO MONTH or DAY TO SECOND but not across month/day.


If the difference might be more than 9999 days (= 27 years) you can go for a UDF like the one found in this thread: 

Subtracting Timestamps as decimals hours

Re: dateiff / dateadd function

your detailed explanation helped me alot!

thank you:)

Teradata Employee

Re: dateiff / dateadd function

FYI, interval precision increase to remove the small limits on interval types is being designed and is intended to be included in an upcoming 16.x release. Possibly 16.50.