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.

Isaac · Accepted Answer · 2017-01-20 17:25:41Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related