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, 2010 at 12:53 PM in Default

avatar image

siera_gld
1k 82 88 93

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, 2010 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, 2010 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, 2010 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, 2010 at 01:34 PM

avatar image

Oleg
17.1k 3 7 28

Thank you - that was the trick oleg!!!

Oct 21, 2010 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.

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:

x425
x33
x6

asked: Oct 21, 2010 at 12:53 PM

Seen: 2899 times

Last Updated: Oct 21, 2010 at 01:29 PM

Copyright 2016 Redgate Software. Privacy Policy