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
– Ross Bush, Commented Dec 7, 2016 at 3: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
– Bobby Jobsite, Commented Dec 7, 2016 at 15:54

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

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

answered Dec 7, 2016 at 15:02

Ciarán

3,0571 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bobby Jobsite Over a year ago

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

Gord Thompson Over a year ago

@BobbyJobsite - You could also accept this answer.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related