linked server, open query using variable for date field

Question

The below SQL query is being run on SQL Server 2005/2008 using linked SQL (Oracle) and I get an error.

Declare @PriorMonth_StartDate Datetime
Declare @PriorMonth_EndDate Datetime

Set @PriorMonth_StartDate = '2012-03-01'
Set @PriorMonth_EndDate =   '2013-10-31'

EXEC('
SELECT      *
INTO #C_INFO
FROM  OPENQUERY(ORACLE_CMIDW1,''
  SELECT A.CID, A.ANO, A.COMP_REVD_DATE, A.REVIEW_COMP_DATE, 
        A.ISSUE, B.ENT_ID, A.TYPE
FROM XXX B 
INNER JOIN YYY A ON B.ANO = A.ANO   WHERE A.REVIEW_COMP_DATE Between ''' + 
    @PriorMonth_StartDate + ''' And ''' + @PriorMonth_EndDate 
+ ''' AND Not A.ISSUE = 110 AND A.TYPE = 1 and B.ENT_ID In (2,3) 
'')')

Here is the error message:

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'Mar'.

Question: There is any connection between the error message and the SQL query? It looks like SQL is reading the date as March 01 2013 or something. If that's the case, it doesn't work because Oracle has it in a different format.

John Tseng · Accepted Answer · 2013-07-01 17:23:18Z

2

The error is in your query construction. Let's go through your quotes. Two single quotes makes a quote in the string that's to be EXECed. Hence, right after ORACLE_CMIDW1, you start the string to send to Oracle. Right after BETWEEN, you end the string with another two single quotes. Hence, the string you actually construct for EXEC looks like:

SELECT      *
INTO #C_INFO
FROM  OPENQUERY(ORACLE_CMIDW1,'
  SELECT A.CID, A.ANO, A.COMP_REVD_DATE, A.REVIEW_COMP_DATE, 
        A.ISSUE, B.ENT_ID, A.TYPE
FROM XXX B 
INNER JOIN YYY A ON B.ANO = A.ANO   WHERE A.REVIEW_COMP_DATE Between 'MAR 1, 2012' And 'MAR 5, 2013' AND Not A.ISSUE = 110 AND A.TYPE = 1 and B.ENT_ID In (2,3) ')

It should be fairly clear from the code coloring that your string terminated before you wanted it to. You should escape the quote with more single quotes like:

INNER JOIN YYY A ON B.ANO = A.ANO   WHERE A.REVIEW_COMP_DATE Between ''''' +

answered Jul 1, 2013 at 17:23

John Tseng

6,3822 gold badges29 silver badges38 bronze badges

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

7 Comments

user1810575 Over a year ago

thanks for your quick response jtseng, i understand what you're saying but my date(s) are messed up in above. date needs to be yyyy-mm-dd format in variable. I know how to write sql with dates hard corded but my problem happens when passing dates thru variable. Can you re write above query so that i can see what you did. Is that even possible. Let me know.

user1810575 Over a year ago

SELECT * INTO #C_INFO FROM OPENQUERY(ORACLE_CMIDW1,' SELECT A.CID, A.ANO, A.COMP_REVD_DATE, A.REVIEW_COMP_DATE, A.ISSUE, B.ENT_ID, A.TYPE_ID FROM XXX B INNER JOIN YYY A ON B.ANO = A.ANO WHERE A.REVIEW_COMP_DATE Between ''2012-03-01'' And ''2013-10-31'' AND Not A.ISSUE = 110 AND A.TYPE_ID = 1 and B.ENT_ID In (2,3) ') -- this sql works perfect but look at the dates hard coded manually, i dont want to do that.

John Tseng Over a year ago

@user1810575 You can convert the datetime variable. Available formats are shown: msdn.microsoft.com/en-us/library/ms187928.aspx Unfortunately, I don't see yyyy-mm-dd. You can use style 111 and then use REPLACE to change '/' to '-'.

user1810575 Over a year ago

can you shoot me a simple example(with date format variable) or use above sql i sent you. Sorry but i can't seem to get it right somehow. thx

John Tseng Over a year ago

@user1810575 Here's an example on how to convert the date: select replace(convert(varchar(max),getdate(), 111), '/', '-')

|

Collectives™ on Stack Overflow

linked server, open query using variable for date field

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related