sqlsql-serverdatetimeopenedgeprogress-db

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


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 14.0) 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.


Solution

  • 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.