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

[ExcessionOCP]

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

[Prudvi]

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

[ExcessionOCP · Accepted solution]

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!

[shivakumara]

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