x

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) 

--------------------------------------------------------------------------------
more ▼

asked Oct 21 '10 at 12:53 PM in Default

siera_gld gravatar image

siera_gld
1k 74 80 83

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.
Oct 21 '10 at 01:31 PM WilliamD
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.
Oct 21 '10 at 02:40 PM siera_gld

@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?
Oct 21 '10 at 03:08 PM Oleg
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Oct 21 '10 at 01:34 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

Thank you - that was the trick oleg!!!
Oct 21 '10 at 03:35 PM siera_gld
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x375
x26
x7

asked: Oct 21 '10 at 12:53 PM

Seen: 2142 times

Last Updated: Oct 21 '10 at 01:29 PM