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

(1) Tag with the database you are using. (2) Explain *what* number you want for a given date. — Gordon Linoff, Apr 21 '20 at 14:03
If you convert a normale datatime to a decimal this is the result — Chris America, Apr 21 '20 at 14:19
For example if you run this statement select cast(cast(getutcdate() as datetime)as decimal(18,8) then you get a decimal like this 43940.59196 — Chris America, Apr 21 '20 at 14:21
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, Apr 21 '20 at 14:53
Why mess around with a decimal of a datetime and not just use the character representation of datetime? where ord_LastUpdateTime > { ts '2020-04-17 12:20:30' } should work - you will need to populate the correct value between the quotes. — Stefan Drissen, Apr 21 '20 at 15:00

score 4 · Accepted Answer · answered Apr 21 '20 at 17:27

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.

Thanks - vote up will become available when you hang around long enough ;-) — Stefan Drissen, Apr 22 '20 at 08:29

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

1 Answers1