[Stackoverflow] [Progress OpenEdge ABL] How to convert CSV TEXT DATE To DATE in WHERE clause

[Jack Williams]

I am having to pull data from a CSV ODBC for a report i am running. One of the issues i am coming across is that the Invoice Date has become stored as text. The file i am pulling from has over 15 months of data but i am only look to capture data from the past 3 months.

When i use the standard WHERE clause saying WHERE invoice date is greater than 2019-07-01, this doesn't effect the data other than remove records with no invoice date.

I am looking for a way in my WHERE clause i can convert this text into the DATE format required to allow me to pull the relevant information. I have tried the below code but it appears the Progress ODBC does not like this as i get an error of: Undefined Function 'CONVERT' in expression.

Please see code below:

SELECT
AEOrdersReceived.`Order Company`,
AEOrdersReceived.`Product Description`,
AEOrdersReceived.`Product Item Ordered Outwork Cost`,
AEOrdersReceived.`Sop Order Status`,
AEOrdersReceived.`Order Method`,
AEOrdersReceived.`Product Item Ordered VA Value`,
AEOrdersReceived.`Product Item Ordered Consumable Cost`,
AEOrdersReceived.`Product Item Ordered Estimated Outwork Cost`,
AEOrdersReceived.`Product Item Ordered Material Cost`,
AEOrdersReceived.`Order Date`,
AEOrdersReceived.`Product Item Ordered Estimated Material Cost`,
AEOrdersReceived.`Payment Method`,
AEOrdersReceived.`Job Number`,
AEOrdersReceived.`Product Item Required Date`,
AEOrdersReceived.`Product Item First Delivery Date`,
AEOrdersReceived.`Sop Order Number`,
AEOrdersReceived.`Product Item Despatched Consumable Cost`,
AEOrdersReceived.`Product Item Despatched Outwork Cost`,
AEOrdersReceived.`Product Item Ordered Estimated Material Cost`,
AEOrdersReceived.`Product Item Despatched Material Cost`,
AEOrdersReceived.`Product Item Despatched VA Value`,
AEOrdersReceived.`Product Item Value`,
AEOrdersReceived.`Product Item Ordered Estimated Consumable Cost`,
AEOrdersReceived.`Product Item Ordered Estimated VA Value`,
AEOrdersReceived.`Invoice Number`,
AEOrdersReceived.`Invoice Date`,
AEOrdersReceived.`Product Item Invoiced Sales Value`
FROM AEOrdersReceived.csv AEOrdersReceived
WHERE AEOrdersReceived.`Sop Order Number` >= 1168593
AND CONVERT(VARCHAR, AEOrdersReceived.`Invoice Date`,102) > 2019-07-01




Continue reading...