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;

[dmxmikey]

SQL##f - SqlState: S1000, ErrorCode: 4294757240, ErrorMsg: [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "[act-date] <= "09022018"" (10713)

Conprice:

SQL SELECT "act-date",

    "c-type",

    "claim-back",

    "con-group",

    "con-type",

    cost,

    "cost-rbon",

    "cost-uplift",

    "cust-code",

    "deact-date",

    depot,

    discount,

    "input-date",

    "last-sold",

    "last-update",

    "list-price",

    lwsv,

    "net-cost",

    "o-ordqty",

    operator,

    "p-code",

    qty,

    "qty-in",

    reported,

    "sell-price",

    "tran-stat-e",

    "tran-stat-s",

    "upd-date",

    weight,

    wsv

FROM PUB.conprice

Where [act-date] <= "09022018"

[neelamsaroha157]

Can you share how the actual values in the field act-date looks like.

Load only this field without any where clause.

[dmxmikey]

[lcontezini]

Your date format is not that you said before. Try this:

LET vMax_Date = '"'&date(today(),'DD/MM/YYYY')&'"';

Conprice:

SQL SELECT "act-date",

    "c-type",

    "claim-back",

    "con-group",

    "con-type",

    cost,

    "cost-rbon",

    "cost-uplift",

    "cust-code",

    "deact-date",

    depot,

    discount,

    "input-date",

    "last-sold",

    "last-update",

    "list-price",

    lwsv,

    "net-cost",

    "o-ordqty",

    operator,

    "p-code",

    qty,

    "qty-in",

    reported,

    "sell-price",

    "tran-stat-e",

    "tran-stat-s",

    "upd-date",

    weight,

    wsv

FROM PUB.conprice

Where [act-date] <= $(vMax_Date);

[alis2063]

Conprice:

SQL SELECT "act-date",

FROM price

WHERE CAST([act-date] AS DATE)=GETDATE();

tRY IT.

[dmxmikey]

SQL##f - SqlState: S1000, ErrorCode: 4294757240, ErrorMsg: [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "[act-date] <= "09/02/2018"" (10713)

Conprice:

SQL SELECT "act-date",

    "c-type",

    "claim-back",

    "con-group",

    "con-type",

    cost,

    "cost-rbon",

    "cost-uplift",

    "cust-code",

    "deact-date",

    depot,

    discount,

    "input-date",

    "last-sold",

    "last-update",

    "list-price",

    lwsv,

    "net-cost",

    "o-ordqty",

    operator,

    "p-code",

    qty,

    "qty-in",

    reported,

    "sell-price",

    "tran-stat-e",

    "tran-stat-s",

    "upd-date",

    weight,

    wsv

FROM PUB.conprice

Where [act-date] <= "09/02/2018"

[neelamsaroha157]

SQL SELECT "act-date"

FROM PUB.conprice

Where CONVERT(Date, act-date) <= '2017-09-25'

;

for Today's date, Use -

SQL SELECT "act-date"

FROM PUB.conprice

Where CONVERT(Date, act-date) <= GETDATE()

;

[dmxmikey]

SQL##f - SqlState: S1000, ErrorCode: 4294757240, ErrorMsg: [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "Date, act-date) <= GETDATE() " (10713)

Conprice:

SQL SELECT "act-date",

    "c-type",

    "claim-back",

    "con-group",

    "con-type",

    cost,

    "cost-rbon",

    "cost-uplift",

    "cust-code",

    "deact-date",

    depot,

    discount,

    "input-date",

    "last-sold",

    "last-update",

    "list-price",

    lwsv,

    "net-cost",

    "o-ordqty",

    operator,

    "p-code",

    qty,

    "qty-in",

    reported,

    "sell-price",

    "tran-stat-e",

    "tran-stat-s",

    "upd-date",

    weight,

    wsv

FROM PUB.conprice

Where CONVERT(Date, act-date) <= GETDATE()

[dmxmikey]

[alis2063]

Hi Mike,

It will sure help

Conprice:

SQL SELECT "act-date",

FROM price

WHERE CAST([act-date] AS DATE)=cast(GETDATE()as date);