Oracle Linked Server in SQL Server 2012 - query with DATE column not working

Question

Queries:

SELECT DISTINCT RCPCODE
FROM SMC..ICWGHC.C_JOBINFOHISTORY 
WHERE UPDATE_DATETIME >= '2017-01-14 13:08:49.0000000' 
  AND UPDATE_DATETIME <= '2017-01-16 13:08:49.0000000'

SELECT DISTINCT RCPCODE 
FROM SMC..ICWGHC.C_JOBINFOHISTORY 
WHERE COMPLETE_DATETIME BETWEEN '2017-01-16 06:52:38.0000000' 
                            AND '2017-01-16 12:52:38.0000000'
ORDER BY COMPLETE_DATETIME DESC

Both the queries with BETWEEN and ">=" did not work for condition in Date column.

If I use this query:

SELECT DISTINCT RCPCODE 
FROM SMC..ICWGHC.C_JOBINFOHISTORY

it does return data.

I'd like to filter data with BETWEEN condition for retrieving data, so that I can sort with datetime.

Answer 1

I have a similar situation with an Oracle linked server in SQL Server. I had not used the syntax you have since we usually use OPENQUERY for this sort of thing so I thought I would give it a try.

There is some sort of issue with the string to date conversion that implicitly happening. Try this...

SELECT DISTINCT RCPCODE 
FROM SMC..ICWGHC.C_JOBINFOHISTORY 
WHERE COMPLETE_DATETIME BETWEEN CONVERT(DATETIME, '2017-01-16 06:52:38', 121)
                            AND CONVERT(DATETIME, '2017-01-16 12:52:38', 121)
ORDER BY COMPLETE_DATETIME DESC

Note that I removed the trailing zeros from the date/time string. They were causing me trouble.

Here is what you would do to use OPENQUERY to do a passthrough query to Oracle...

SELECT * 
FROM OPENQUERY(SMC, '
    SELECT DISTINCT RCPCODE 
    FROM ICWGHC.C_JOBINFOHISTORY 
    WHERE COMPLETE_DATETIME BETWEEN ''2017-01-16 06:52:38''
                AND ''2017-01-16 12:52:38''
    ORDER BY COMPLETE_DATETIME DESC')

I again removed the trailing zeros.

Do either of those work for you?

Noel