Best workaround for filtering by dates in a SQL collection

[eyoder]

Hello! We have a use case where we only want to query records that have a field with a date between 2 values. (If it matters, they are coming from a csv and will go back into a smaller csv.) The problem is that you can't reference a date in the SQL collection by Workato query. What creative solutions exist? Workato workbot suggested 'Mapper' but I fail to see how that would apply. Thanks in advance! 

[gary1]

Hi, can you clarify what you mean by this? 

"The problem is that you can't reference a date in the SQL collection by Workato query."

Once you make the Collection, you use another Collection query action to write a SQL query to access its data. You can use comparison operators on date fields to find matches within specific dates.

[eyoder]

Hmm, this is in the docs: https://docs.workato.com/en/features/collection.html#actions under 'collection date error' and I assumed that is why we haven't been able to make this work properly. Thank you for your consideration!

[Bhagya_pola]

That is when you want any dateTime fields in output then you need to convert that dateTime pill in the doc mentioned format if not it throws that error 

[Bhagya_pola]

Hi @eyoder 

I think you can try in the way shown in below attached images, let me know if this doesn't help