How do use DateDiff in Teradata, in my SQL, it works but in Teradata giving me some kind of error

Database
Fan

How do use DateDiff in Teradata, in my SQL, it works but in Teradata giving me some kind of error

SELECT

Branch,


Larmstr01DateMaturity "MaturityDate",

DATEDIFF(day, GetDate(), Larmstr01DateMaturity) "DaystoMaturity,

from LoanMaster

where AccountStatus = 'A'


AND ((DATEDIFF(day, GetDate(), Larmstr01DateMaturity) >= 15)

AND (DATEDIFF(day, GetDate(), Larmstr01DateMaturity) <=60))

 

The message I'm getting is "Syntax Error: expected something between '(' and and 'day' keyword.

Statemet 1: Unknown failed.

5 REPLIES
Senior Apprentice

Re: How do use DateDiff in Teradata, in my SQL, it works but in Teradata giving me some kind of error

DateDiff is no Standard SQL function, in your case it's easy to translate:

SELECT

Branch,

Larmstr01DateMaturity "MaturityDate",

current_date - Larmstr01DateMaturity AS DaystoMaturity

from LoanMaster

where AccountStatus = 'A'

AND DaystoMaturity between 15 and 60

Dieter

Fan

Re: How do use DateDiff in Teradata, in my SQL, it works but in Teradata giving me some kind of error

Thanks!!

Fan

Re: How do use DateDiff in Teradata, in my SQL, it works but in Teradata giving me some kind of error

Hi Dieter,

It doesn't work. I'm trying to get the number of days to maturity using the Larmstr01DateMaturity as the main field I'm comparing to the current date.

In my T-SQL query, it works, my code is like this:

SELECT

Larmstr00Branch as [Branch],

Larmstr01DateMaturity as [Maturity Date],

DATEDIFF(day, GetDate(), Larmstr01DateMaturity) as [Days to Maturity]

from LoanMaster

WHERE AccountStatus = 'A'

AND DATEDIFF(day, GetDate(), Larmstr01DateMaturity) >= 30

and DATEDIFF(day, GetDate(), a.Larmstr01DateMaturity) <=90

 

But when i ran it in Teradata, this error is what i get:


SELECT

Larmstr00Branch "Branch",

Larmstr01DateMaturity "MaturityDate",

Current_date - Larmstr01DateMaturity "DaystoMaturity"

from LoanMaster

where AccountStatus = 'A'

AND DaystoMaturity between 30 and 90

I'm getting this error:

Executed as Single statement.  Failed [5407 : HY000] Invalid operation for DateTime or Interval.

Elapsed time = 00:00:00.016

STATEMENT 1: Select Statement failed.

Senior Apprentice

Re: How do use DateDiff in Teradata, in my SQL, it works but in Teradata giving me some kind of error

Despite the name (Larmstr01DateMaturity) seems to be a Timestamp, try CAST(Larmstr01DateMaturity as DATE) instead.

Dieter

Enthusiast

Re: How do use DateDiff in Teradata, in my SQL, it works but in Teradata giving me some kind of error

Hi Dieter,

How would we transform the following expression to Teradata equivalent:

CASE
      WHEN DATEADD(hour, DATEDIFF(hour, 0, a.BeginDTS), 0) = DATEADD(hour, DATEDIFF(hour, 0, a.EndDTS), 0)
    THEN DATEDIFF(minute,EndDTS,BeginDTS)/60.0
      ELSE 1.00
END as HourAMT

***NOTE: BeginDTS, EndDTS are timestamp(3) fields.

Please help!

Thanks,

Shardul