Oracle Date Conversion in Openquery

Question

I have a query which I'm trying to convert Oracle date format to a MM - YYYY so I can group by month and year before I pull it to the sql server for results... the format and grouping needs to take place before it comes to sql...

The TO_CHAR Oracle syntax gives an error in the formatting section of MON in the attached code. I've tried two single quotes and the Full word Month with no success

DECLARE @EM_ITEM_NUM VARCHAR(10)
DECLARE @sqlQuery VARCHAR(8000)
DECLARE @finalQuery VARCHAR(8000) 

SET @EM_ITEM_NUM = '1171289' 

SET @sqlQuery = 'select
    (oss.SLS_QTY) as QTY
    ,(oss.SLS_AMT) as AMT
    , TO_CHAR(oss.INVC_DT, 'MON, YYYY') as my_date
    ,(oss.INVC_DT) AS MYDATE
    ,oss.EM_ITEM_NUM
    ,oss.SLS_CUST_BUS_TYP_CD
    ,oss.CNTRC_LEAD_TP_ID
    from dcp0.V_SALE_ITEM oss 
    WHERE EM_ITEM_NUM in (' + '''' + '''' + @EM_ITEM_NUM + '''' + ''')'

SET @finalQuery = 'SELECT * FROM OPENQUERY(OPS_ORACLE_IW,' + '''' + @sqlQuery + '''' + ')' 


EXEC(@finalQuery) 

--------------------------------------------------------------------------------

Answer 1

0

I believe that your set @sqlQuery part should have 'MON, YYYY' spelled as ''''MON, YYYY'''', like this:

SET @sqlQuery = 'select
    (oss.SLS_QTY) as QTY
    ,(oss.SLS_AMT) as AMT
    , TO_CHAR(oss.INVC_DT, ''''MON, YYYY'''') as my_date
    ,(oss.INVC_DT) AS MYDATE
    ,oss.EM_ITEM_NUM
    ,oss.SLS_CUST_BUS_TYP_CD
    ,oss.CNTRC_LEAD_TP_ID
    from dcp0.V_SALE_ITEM oss 
    WHERE EM_ITEM_NUM in (' + '''' + '''' + @EM_ITEM_NUM + '''' + ''')'

Oleg

answered Oct 21 '10 at 01:34 PM

Oleg
15.4k ● 1 ● 4 ● 24

Thank you - that was the trick oleg!!!

Oct 21 '10 at 03:35 PM siera_gld

(comments are locked)

Oracle Date Conversion in Openquery

Follow this question

Related Questions