Is there a way to use SQL functions (like LEFT() and RIGHT()) in a QueryBuildRange?
(I’m pretty sure the answer to this question is ‘no’, but I thought I’d ask anyway.)
I want to filter a query for a form that’s used to populate a drop-down, so expired credit cards don’t show. And the credit card expiration dates are stored as a string, in form “MMYY”.
So I want to do something like this:
ds = custCreditCard_ds.query().dataSourceTable(tableNum(CreditCardCust));
qbr = ds.addRange(fieldNum(CreditCardCust, ExpiryDate));
qbr.value(’(RIGHT(EXPIRYDATE, 2) > “14”) || ((RIGHT(EXPIRYDATE, 2) == “14”) && (LEFT(EXPIRYDATE, 2) >= “12”))’);
…but of course that doesn’t work.
I’m trying to think up a creative way to do this, and I’m not coming up with one, short of adding a new field to the table, storing the expiration date as an actual date, or at least reversed to “YYMM”, so I can do a simple >= on it.
Any interesting ideas would be welcome!