CODEWITHSUNDEEP
openedgeprogress-4glprogress-db
CR15 BRN
CR15 BRN

Reputation: 33

How to compare dates in a sql script to progress database?

We have a field in our progress database which is DATETIME-TZ so an example of the data is "23/05/2019 12:11:16.099" - I'm need some help to compare dates in this field with an SQL select statement

In progress procedure editor, I can compare dates easily, for example

select date(bond-no) from accadd where date(bond-no) <= Today

This works perfectly, but i need to get this info via sql for a web page and just get an error, even breaking the field down as a substring and making the date back up doesn't seem to work

SELECT date(""bond-no"") FROM accadd WHERE date(""bond-no"") <= Date()

Have tried variations on the above and this too...

DATE(SUBSTRING(""bond-no"",4,2),SUBSTRING(""bond-no"",1,2),SUBSTRING(""bond-no"",7,4))

This should return just dates earlier than today, but just get the old syntax incorrect message, so i'm missing something somewhere

[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about ") FROM accadd WHERE" (10713)

Upvotes: 0

Views: 4044

Answers (2)

Stefan Drissen
Stefan Drissen

Reputation: 3379

Using the SQL Editor in PDSOE which uses JDBC on a datetime (no -tz) field:

SELECT "bond-no" FROM pub.accadd WHERE "bond-no" <= CURDATE()

To use a static date:

SELECT "bond-no" FROM pub.accadd WHERE "bond-no" <= TO_DATE('5/31/2019')

Or:

SELECT "bond-no" FROM pub.accadd WHERE "bond-no" <= { d'5/31/2019' }

Upvotes: 1

Tom Bascom
Tom Bascom

Reputation: 14020

You do need to quote the field name because it contains a dash but your sample code has doubled quotes. Have you tried:

SELECT date("bond-no") FROM accadd WHERE date("bond-no") <= Date()

I'm not sure why you are doing it but I don't think that you need the DATE() function around the "bond-no". DATE() is a 4gl function so it works when you are at the "procedure editor" and using the embedded SQL but the OpenEdge SQL92 engine does not have a DATE() function. There is, however, a TO_DATE() function: https://documentation.progress.com/output/ua/OpenEdge_latest/index.html#page/dmsrf%2Fto-date.html%23wwID0EXIOS

The following works with the sports2000 database and using sqlexp (rather than embedded sql at the procedure editor):

select * from pub.order where orderdate < '1/1/1999'

I also added a datetime-tz field called "tstamp", initialized it and tried it like so:

select tstamp from pub.order where tstamp < '1/1/1999'

which worked fine too.

Upvotes: 0

Related Questions