Where clause

[dmxmikey]

I have below script and need to filter data where the field act-date is <= to today and was wondering how to do that.

Conprice:

SQL SELECT "act-date",

FROM price;

[Peter_Cammaert]

SQL is not entirely free from RDBMS-dependent quirks. What works on average is to specify a DATE literal as a database date formatted like 'YYYY-MM-DD' (all numerical values).

If your DBMS follows this simple rule, then this should work:

SELECT * FROM PUB.conprice WHERE [act-date] <= '2018-02-09'

(Note the single quotes)

However, if it fails, you can always revert to the description of the SQL implementation of your particular DBMS which is usually available on-line. The format for date literals is explained here: OpenEdge 11.7 Documentation - Date Literals (and everything else as well SQL-wise, if you navigate through these pages). If the above doesn't work, try

SELECT * FROM PUB.conprice WHERE [act-date] <= { d '2018-02-09' }

which uses a particular formatting style for your DBMS. If that works, try the $-sign substitution trick to put the correct date in the WHERE clause.

LET vToday = Date(Today(), 'YYYY-MM-DD');

SELECT * FROM PUB.conprice WHERE [act-date] <= '$(vToday)'

or

LET vToday = Date(Today(), 'YYYY-MM-DD');

SELECT * FROM PUB.conprice WHERE [act-date] <= { d '$(vToday)' }

Best,

Peter