SQL statement with Date variable

CaroleBPRI

Hello Community,

Being a beginner on Alteryx, here I am faced with my first real problem.

I have in INPUT several SQL queries that use date fields in the WHERE clause.
Each time, I have to enter a where clause with date between 'StartDate' and 'EndDate'. So there are two parameters in each queries. Always the same.

I first tried the "Dynamic input" with the "Modify SQL Query" + Add "replace a specific string"
Text To Replace: 'StartDate'
Replacement Field '2022-01-01'
+
Add "replace a specific string"
Text To Replace: 'EndDate'
Replacement Field '2022-12-31'

=> Resulting in the following error message:
Error: Input Data (8): Error SQLPrepare: [Teradata][ODBC Teradata Driver][Teradata Database] A character string failed conversion to a numeric value.

I also tried with the Action tool, with no more success.

Thank you in advance for your help!

ArnaldoSandoval

Hi @CaroleBPRI 

Implementing Date filters from clients (like Alteryx) are challenging at the begining, for example, the query below:

SELECT * FROM Customer WHERE Last_Payment_Date = <expression>

We usually construct this query like this:

SELECT * FROM Customer WHERE Last_Payment_Date BETWEEN '2023-07-01' AND '2023-07-31'

The problem is most cases is that Last_Payment_Date is usually a DataBase engine Date or DateTime type and we are trying to compare it with an string, It will fail, some ways to handle these scenarios are convert the Last_Payment_Date to string, using the DataBase engine function to do so, or the other way around convert our string dates ('2023-07-01') to a database date using the corresponding function.

I found the function CAST is the one we should use to convert a date field to string Teradata Date Format and Time Values: Common Questions Answered or from the same article, TO_DATE could be use to convert our string dates to the engine dates.

Some warnings:

• This technique works with any database engine as long as we implement the appropiated function.
• If we convert a Date-DateTime field to string, we exclude any index the field may have, e.g. the query will perform slower.

Hope this helps,

Arnaldo