0

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Bobby Jobsite
  • 1
  • 1
  • 2
  • 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 Dec 07 '16 at 03:21
  • 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 Dec 07 '16 at 15:54

1 Answers1

2

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'')

Ciarán
  • 3,017
  • 1
  • 16
  • 20
  • BOOM! You are the best! that did the trick. It also helped me to understand philosophically how to better understand what functions to use through open query. I owe stack overflow some answers to some questions when I get a minute!! – Bobby Jobsite Dec 08 '16 at 14:52
  • @BobbyJobsite - You could also [accept](http://meta.stackexchange.com/a/5235/238021) this answer. – Gord Thompson Dec 08 '16 at 18:23