PROGRESS SYNTAX USING OPENQUERY FOR DATEADD EQUIVALENT

Question

I have an open query I am using to connect to a PROGRESS ODBC, I cannot get the syntax equivalent from SQL of

 SELECT DATEADD(DAY, -1, GETDATE()) 

Answer 1

The Progress SQL documentation is here:

https://documentation.progress.com/output/OpenEdge117/openedge117/#page/dmsrf%2Fadd-months.html%23wwID0EGGFR

There is no DATEADD() function although there is ADD_MONTHS(). I'm not much of a SQL coder but I expect that you could probably cobble something together with the other date related functions.

Answer 2

I came up with the following to get records where the date is greater than two days prior:

where tr_date = 
to_date({ fn convert (month(curdate()), sql_varchar)} + '/' + { fn convert(dayofmonth(curdate()) -2, sql_varchar)} + '/' + { fn convert (year(curdate()), sql_varchar)})

It heavily relies on curdate() which you apparently can't use in a select statement, only in a where clause.

In SQL Server I would have done all of this in front of the query, put the results in a variable and then used the variable in the query's where clause, but Progress also apparently doesn't have the concept of variables.

The problem with the above is that it doesn't account for the beginning of the month where substracting 2 from 1 would result in -1 and therefore an invalid date. So the algorithm to account for this becomes more complicated. All more doable if you had variables but trickier when you have to do it all on the fly.

My purpose was to generate a new query each day for an incremental data integration process using SSIS. I switched over to dynamically generating the query in SQL Server and inserting the results into an SSIS configuration table that the package reads. With that method you end up using good ol' reliable dateadd in SQL Server. That unfortunately doesn't help much if you're trying to do this in Progress with SQL there.