Import data from Oracle

Question

Import data from Oracle

Prudhvi _SQL

SSC Veteran

Points: 209
More actions
June 13, 2010 at 10:04 pm

#236432

Hi Folks,
I have a hard time in importing date for first day of current month to Last day of the current month from oracle source to SQL destination.
Below is the Code I used But I'm unable to pull:
SELECT * FROM Daily_Sales
Where LAST_UPDATE_DATE BETWEEN (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101))
AND
(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101))
Please let me know the code for pulling the Current Month data from oracle source
Please help me With this very urgent.
June 14, 2010 at 7:52 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

dprudhvi.raju (6/13/2010)
I have a hard time in importing date for first day of current month to Last day of the current month from oracle source to SQL destination.
SELECT * FROM Daily_Sales
Where LAST_UPDATE_DATE BETWEEN (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101))
AND
(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101))
Please let me know the code for pulling the Current Month data from oracle source
Please help me With this very urgent.

I understand code is expected to hit Oracle database then...

SELECT *

FROM Daily_Sales

Where to_char(LAST_UPDATE_DATE,'YYYYMM') = to_char(SYSDATE,'YYYYMM')

;

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

sgmunson SSC Guru Points: 110640 More actions · Answer 2

You may also find that it depends on exactly how the import is taking place... I once had to work with data from an Informix database on a Linux Server, where it was an ODBC data source to Reporting Services, and I had to use:

SET DATEFORMAT MDY

to get the two systems to correctly communicate dates across that link. Remember that data in an Oracle database that you have set up as a LINKED server requires PL/SQL commands, not T-SQL ones. Thus GETDATE is SYSDATE and a LOT of the date functionality is rather different. Take a good look on the web for PL/SQL syntax. You may need to use OPENQUERY rather than mere SELECT statements, as SSMS isn't going to understand PL/SQL, and if you need to use PL/SQL because of the differences in date functions, then OPENQUERY is your ticket to doing that.

Steve

(aka sgmunson)

:-):-):-)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Prudhvi _SQL SSC Veteran Points: 209 More actions · Answer 3

Thanks for all the reply's. I will try to implement.. I will get back if its still a problem... Thank you all

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

dprudhvi.raju (6/14/2010)
Thanks for all the reply's. I will try to implement.. I will get back if its still a problem... Thank you all

Glad to help.

When working in mixed environment always test/finetune queries on target database - that will save you a lot of time 😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.