Date & time conversion using oracle linked server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date & time conversion using oracle linked server

Author

Topic

korssane
Posting Yak Master

104 Posts

Posted - 2009-03-29 : 12:13:17

Hi All,
i am using a linked server with openquery to import data from Oracle data base to my sql 2005 server.
I have 2 fields that are supposed to show me date & time but are only showing strings. here is my code :
SELECT TOP 100 * FROM OPENQUERY(LINKED_ORA,
'SELECT
TROUBLEID,
CREATE_DATE,
MODIFIED_DATE
FROM ORACLE.CLIENT_TROUBLE');
GO
here is an example of what the " CREATE_DATE" is showing :
"1034002056" instead of showing a date & time value
any suggestion on how to convert this will be greately appreciated.

thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-29 : 12:58:25

That looks like epoch seconds. If that is the case, you can convert to datetime as:

dateadd(ss, cast(CREATE_DATE as int), '1970-01-01')

korssane
Posting Yak Master

104 Posts

Posted - 2009-03-29 : 13:02:23

Thanks Sunitabeck for the quick reply .
i ll try this and get back to you.

korssane
Posting Yak Master

104 Posts

Posted - 2009-03-29 : 13:04:44

SOrry where i have to put this syntax ?
is it in the query i used to import data ?
and should i remove the CREATE_DATE FIELD and replace it with the synyax yougave me ?

thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-29 : 13:16:40

I know next to nothing about Oracle data types. (Correction: make that "absolutely nothing").

One possibility is to find out what the equivalent conversion syntax in Oracle is and use that in the query you send to the linked server. If the Oracle syntax was exactly identical to MS-SQL syntax (which it probably isn't), then your query would be:

SELECT TOP 100 * FROM OPENQUERY(LINKED_ORA, 
'SELECT 
   TROUBLEID,
   dateadd(ss, cast(CREATE_DATE as int), '1970-01-01') as CREATE_DATE,
   dateadd(ss, cast(MODIFIED_DATE  as int), '1970-01-01') as MODIFIED_DATE
FROM ORACLE.CLIENT_TROUBLE');
GO

The other possibility is to do the conversion on the MS-SQL side. In that case, your query would look like this:

SELECT TOP 100 
TROUBLEID,
dateadd(ss, cast(CREATE_DATE as int), '1970-01-01') as CREATE_DATE,
dateadd(ss, cast(MODIFIED_DATE  as int), '1970-01-01') as MODIFIED_DATE
 FROM OPENQUERY(LINKED_ORA, 
'SELECT 
TROUBLEID,
CREATE_DATE,
MODIFIED_DATE 
FROM ORACLE.CLIENT_TROUBLE');
GO

I know the second query would work. The first query, most likely will not work because of Oracle syntax being different.

That the date format is Epoch seconds is a guess on my part. You may want to get more information on the data to make sure that that guess is correct.

korssane
Posting Yak Master

104 Posts

Posted - 2009-03-29 : 13:35:20

Thanks Bro,
you are right cuz the 2nd syntax woekd fine for me ..the thing is i am using SQl 2005 express and the nidea to import the whole oracle table then do conversion is not a good idea.
i will find out the equivalent on oracle may be..
thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources