What is the syntax for date functions in openquery - OleDB through SQL Server

Question

I'm trying to dynamically pass a date function in an openquery on SQL Server. My SQL Server is connected to and OleDB Oracle database.

When I run my query using an Oracle based date function, my query runs, but returns no data:

SELECT * 
FROM OPENQUERY( [SMA], 'SELECT B.SQL_DATE
                        FROM DIM_DATE B
                        WHERE B.SQL_DATE = current_DATE');

When I run that query using a SQL Server based date function I get the following error message:

SELECT * 
FROM OPENQUERY([SMA], 'SELECT B.SQL_DATE
                       FROM DIM_DATE B
                       WHERE B.SQL_DATE = ''GETDATE()''');

OLE DB provider "OraOLEDB.Oracle" for linked server "SMA" returned message "ORA-01841: (full) year must be between -4713 and +9999, and not be 0".

I think I'm supposed to be using SQL Server based syntax (as opposed to Oracle syntax), but not sure how the current_DATE statement is even able to run?

Any help would be much appreciated!

The 'GETDATE()' is being replaced by the SQL Server parser's date representation because the evaluation is not in the string literal, whereas, current_DATE is being sent over the wire to the connected server and being parsed as sql there. Check your locale and make sure the date values align. You can force a specific date value and it sounds like that is what you need to do here. — Ross Bush
So part of the problem is the date field I am referencing is a timestamp. If I am querying the database outside of the open query I get get this to work: SELECT B.SQL_DATE FROM [SMA]..[SMA].[DIM_DATE] B WHERE B.SQL_DATE = Convert(date, GETDATE()); However, I can't see to figure out what set of ticks I need to use, or if it is even possible to pass that function through openquery. — Bobby Jobsite

Ciarán Ciarán · Accepted Answer · 2016-12-07T15:02:52

You use the syntax that your target database supports, so in this case you use Oracle syntax.

GetDate() equivalent is SYSDATE. This contains a time so you can remove the time by speciying TRUNC(SYSDATE). For date literals you would need to specify a TO_DATE() function, E.g. TO_DATE('31-DEC-2016','DD-MON-YYYY')

It gets messy when using OpenQuery as date literals would have to be double single quotes, i.e. TO_DATE(''31-DEC-2016'',''DD-MON-YYYY'')

What is the syntax for date functions in openquery - OleDB through SQL Server

1 Answers