Convert datetime (YYYY-MM-DD HH:MM:SS) to decimal for openquery to progress database

Question

I am trying to convert the current date to a decimal number. The reason I want to get a decimal number is to convert that decimal number to a Julian date. Because of performance reasons I need to do this with an OPENQUERY. The linked database is a progress database.

The connection is between a SQL database (sql server 2008r2) and progress database (openedge)

Current situation:

SELECT * FROM OPENQUERY([MylinkedServerName],'select * FROM [databasetable] WHERE ord_LastUpdateTime > (CAST(CAST(GETUTCDATE() as datetime) as decimal(18,8) + 2415020.5)

This is the error:

OLE DB provider "MSDASQL" for linked server "[MylinkedServerName]" returned message
"[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]
Syntax error in SQL statement at or about
") as datetime) AS DECIMAL(18,8)) + 24150" (10713)".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query
"SELECT * FROM [databasetable] WHERE ord_LastUpdateTime > (CAST(CAST(GETUTCDATE() as datetime) AS DECIMAL(18,8)) + 2415020.5)"
for execution against OLE DB provider "MSDASQL" for linked server "[MylinkedServerName]".

I hope you have enough information to help me.

(1) Tag with the database you are using. (2) Explain what number you want for a given date. — Gordon Linoff
If you convert a normale datatime to a decimal this is the result — Chris America
You know that Sql Server 2008 R2 has gone end of life, right? That means it no longer gets any updates... not even critical security patches! It's dangerous and irresponsible to still be using it. Upgrading to a newer version should be priority #1 here. — Joel Coehoorn

Stefan Drissen Stefan Drissen · Accepted Answer · 2020-04-21T17:27:11

To get started, using timestampdiff you can calculate the 'time' since Julian epoch:

timestampdiff( sql_tsi_second, { d '01/01/-4713' }, now() ) / 86400

Since the unit for the difference is sql_tsi_second, the result needs to be divided by 24 hours * 60 minutes * 60 seconds = 86400 seconds. If you want a more precise calculation you can use:

timestampdiff( sql_tsi_frac_second, { d'01/01/-4713' }, now() ) / 86400000

The only remaining problem is that I do not think that now() is UTC - so you will need to correct for number of hours between UTC and your timezone.

The above have been tested using JDBC, meaning that to use it in OPENQUERY may require some extra quote fiddling.

Convert datetime (YYYY-MM-DD HH:MM:SS) to decimal for openquery to progress database

1 Answers