in-db teradata date add function (interval) defect observed

[davidhenington]

looping in @bora_perusic 

Hello, 

I believe I have identified a defect (I would call it a defect as the interval function should be supported) with formula in-db and the interval function in Teradata. 

Where the field "Date of Service" is a DATE field as required: 

returns: 

Typically Alteryx in-db generates syntax that can differentiate between column aliases and strings. Here the syntax seems to be generating what the database sees as a string. 

Since the query fails I cannot generate the code with dynamic output in-db. 

Best - 

David

[patrick_mcauliffe]

It's been a little while since I've been active in Teradata, but I believe the correct syntax requires there to be single quotes after the word "date", around the date string.

Try this:

date'"Date of Service"'+ interval '1' day

[davidhenington · Accepted solution]

Update: NOT a defect

Since i'm passing along a dynamic int value as the interval 'amount', i have to cast the dynamic value as INTERVAL, which is an actual data type in Teradata! 

in formula in-db this looks like this: 

"Date of Service" + cast("Adjustment" as interval day)

Success! Works perfectly. 

[davidhenington]

HI Patrick, the problem at that point was actually the word DATE. Once I removed that, it ran successfully. 

Then I had to solve the INTERVAL problem, which was the critical blocker. 

[bora_perusic]

Thanks, @davidhenington !

[davidhenington]

no problem @bora_perusic ! I can just imagine how challenging it is to deliver all this functionality on all these different databases with their different SQL dialects!