dateiff / dateadd function

Database
Enthusiast

dateiff / dateadd function

hi :)

 

someone know how convert to teradata:

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

 

 

thanks!!

 

7 REPLIES
Junior Contributor

Re: dateiff / dateadd function

Similar to your last question:

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

Re: dateiff / dateadd function

ok thanks!

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

Junior Contributor

Re: dateiff / dateadd function

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

Enthusiast

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?

Junior Contributor

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

Enthusiast

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.