topic WHERE Clause in SQL connector has errors and I have no idea why in Workato Pros Discussion Board

WHERE Clause in SQL connector has errors and I have no idea why

ExcessionOCP — Wed, 15 Jan 2025 12:56:21 GMT

Hi All,

I'm trying to return records from a SQL database where the dates in the "lastcastingdate" column of the DB is more than 14 days ago. I have entered the following into the WHERE clause in the Select Row action:

=LASTCASTINGDATE < today - 14.days

but it just fails when I try to save it. The error message is "Formula has errors" and that's it. Can anyone shed any light on this?

Cheers,

Phil

Re: WHERE Clause in SQL connector has errors and I have no idea why

Prudvi — Wed, 15 Jan 2025 14:00:42 GMT

Hi @ExcessionOCP,
If the data type of "lastcastingdate" is date , then please compare the format of the "lastcastingdate" and "today - 14.days".
As the format of "today" formula is "YYYY-MM-DD".
If the format does not match you might get formula error.
Regards,
Prudvi

Re: WHERE Clause in SQL connector has errors and I have no idea why

ExcessionOCP — Wed, 15 Jan 2025 14:29:17 GMT

Thanks for your reply. I was just about to close this post as I used a different approach - I wrote the query in TSQL and then executed it using the Custom SQL option within Workato. Thanks for taking the time to reply though!

Re: WHERE Clause in SQL connector has errors and I have no idea why

shivakumara — Wed, 15 Jan 2025 15:05:09 GMT

Hi @ExcessionOCP ,

If you're using MySQL, the assumption about today - 14 not being appropriate is correct. Instead, you should use the SYSDATE() function to calculate dates. Here's the revised suggestion:

where order_date < SYSDATE() - INTERVAL 14 DAY;
Would you like further clarification or assistance? 😊

Thanks and Regards,
Shivakumara K