SQL (in Progress Database) - Date - Error 7481 - Inconsistent Types

Question

Using SQL on a Progress Database (SQL92)

Using a piece of software (designed for Pervasive) that allows writing of reports etc, which can be emailed to users

I've been receiving the following error with my SQL:

SQL Debug: [DataDirect][ODBC PROGRESS Driver][PROGRESS]Inconsistent Types (7481)

The following code works perfectly:

Select * FROM pub."stockmaster"
Where ("sm-sold" = 0 OR ("sm-sold" = 1 AND "sm-selldate" >= '2017-06-01'))

The problem (error) comes when I use the software's built-in 'variables', such as @MONTHSTART@ which displays as '01 June 2017'

Select * FROM pub."stockmaster"
Where ("sm-sold" = 0 OR ("sm-sold" = 1 AND "sm-selldate" >= @MONTHSTART@))

I need to use this 'System Variable' so I don't have to keep changing the SQL date manually every month

The Field "sm-selldate" is formatted as type Date in Progress

These system variables work perfectly fine on a Pervasive Database

I've searched couple forums and seen suggestions of CONVERT(DATETIME, but no workable solution

Version of Progress? Always good to include that since behavior might have changed. — Jensd

Jensd Jensd · Accepted Answer · 2017-06-12T10:33:35

Ouch, 9.1E is ancient...

Perhaps a combination of CURDATE, TO_CHAR and TO_DATE could do it?

I don't run SQL towards OpenEdge so I cannot really test this.

TO_DATE(TO_CHAR(MONTH(CURDATE()) + "/1/" + TO_CHAR(YEAR(CURDATE()))

My intention ist that the string within TO_DATE should resolve to something like

'6/1/2017'

(First of June 2017).

Here's the documentation for the 9.1E SQL-92: