question

siera_gld avatar image
siera_gld asked

Oracle Date Conversion in Openquery

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) --------------------------------------------------------------------------------
oracleopenquerydateformat
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Just a question - why does the date *have* to be converted before coming to sql server? You could do it when the data arrives too you know.
2 Likes 2 ·
There is a huge efficiency benefit to only bringing in data you need - The oracle server is a transactional system and I dont need that level of detail in my results - one item took over 4 minutes to render from this system.
0 Likes 0 ·
@siera_gld In this case you might want to consider removing the line reading
,(oss.INVC_DT) AS MYDATE
from your select because now you return both the datetime column and its MON - YYYY representation. By the way, did adding 3 single quotes on either side of the **'MON, YYYY'** (like in my answer) help to resolve the problem?
0 Likes 0 ·

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you - that was the trick oleg!!!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.