Database
Enthusiast

hi :)

someone know how convert to teradata:

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

thanks!!

7 REPLIES
Junior Contributor

## Re: dateiff / dateadd function

`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:)