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
from dcp0.V_SALE_ITEM oss WHERE EM_ITEM_NUM in (' + '' + '' + @EM_ITEM_NUM + '' + '')'
SET @finalQuery = 'SELECT * FROM OPENQUERY(OPS_ORACLE_IW,' + '' + @sqlQuery + '' + ')'
View Complete Post