Reading microseconds from Sql server into oracle 10g

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Working with Oracle » Reading microseconds from Sql server into...

18 posts, Page 1 of 2

1 2 »»

Reading microseconds from Sql server into oracle 10g

Rate Topic

Display Mode

Topic Options

Author

Message

caramelkaps

Posted Tuesday, April 10, 2012 12:52 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 11, 2012 2:38 PM
Points: 8, Visits: 13

Hi,

We have a very time sensitive pressing requirements to be addressed immediately.

We need to read the date time column from sql server into oracle 10g.

We have a dblink established between the two servers and have tried to use sql server and sql native client 10 driver to read the dates from sql into oracle.This we are able to do very successfully.
The issue is in reading microseconds stored in sql server.The microsecond part comes in as 00000 from sqlserver to oracle.These microseconds are needed to identlfy the unique records.

For instance

SQL SERVER Oracle
source_id source id
01/01/2012 6:30:35:456 01/01/2012 6:30:35:000000.

Any insight or help will be much appreciated.

We have no control over sql server Db as this is remote third party DB.We just have select access to their tables to bring into our own oracle DB.

Post #1281060

Lynn Pettis

Posted Tuesday, April 10, 2012 1:02 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588, Visits: 27,375

caramelkaps (4/10/2012)

SQL Server's datetime data type only records time down to the milliseconds, and then only to .000, .003, and .007.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1281075

caramelkaps

Posted Tuesday, April 10, 2012 1:08 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 11, 2012 2:38 PM
Points: 8, Visits: 13

I meant to say milliseconds....whatever it records after seconds I want to bring it over to oracle.

any value after seconds is currently becoming 00000 in oracle.

Also I tried using to_char and giving it a format 'dd/mm/yyyy hh24:mi:ss:ssss' and insert it into oracle varchar column but it just repeats the seconds value

For eg
sql server Oracle
source_id source id
01/01/2012 6:30:45:346 01/01/2012 6:30:45:4545 .

Thanks.

Post #1281082

Lynn Pettis

Posted Tuesday, April 10, 2012 1:12 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588, Visits: 27,375

caramelkaps (4/10/2012)

How are you pulling the data over?

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1281086

caramelkaps

Posted Tuesday, April 10, 2012 1:20 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 11, 2012 2:38 PM
Points: 8, Visits: 13

Through an ODBC Connection.
Sql Server -2008
Using SQL SERVER Native client 10.0 driver version 2009.100.1600.01

Then created a dblink in oracle to use the ODBC connection to get the data.

We have Oracle 10g.

Post #1281100

Lynn Pettis

Posted Tuesday, April 10, 2012 1:24 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588, Visits: 27,375

Out of my depth. Oracle experience is light and I didn't have to pull data from a SQL Server database over a DBLINK.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1281102

Lowell

Posted Tuesday, April 10, 2012 1:36 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:55 AM
Points: 11,605, Visits: 27,643

are you using the Oracle TO_DATE() function to convert the data?
what is your string format for the conversion?

edit
jsut read you need to use TO_TIMESTAMP() in oracle to get fractions of seconds:

SELECT  TO_TIMESTAMP('2004-09-30 23:53:48,140000000', 'YYYY-MM-DD HH24:MI:SS,FF9') 
FROM    dual

http://stackoverflow.com/questions/1758219/string-to-date-in-oracle-with-milliseconds

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1281112

caramelkaps

Posted Tuesday, April 10, 2012 2:05 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 11, 2012 2:38 PM
Points: 8, Visits: 13

select to_char(sysdate,'mm/dd/yyyy hh24:mi:ss.FF') from dual----
this gives me an error date format not recognized in oracle

select to_char(sysdate,'mm/dd/yyyy hh24:mi:ss.ssss') from dual
gives me
04/10/2012 16:00:36.3636
Note that milliseconds is just a repeat of seconds

If I try this from oracle to sql server--gives me an error : format code appears twice
select to_timestamp(source_id,'yyyy-mm-dd hh24:mi:ss:ssss') from xyz@dblink_test;

If I issue this query ----it does not give me milliseconds just repeats the seconds
select to_char(source_id,'yyyy-mm-dd hh24:mi:ss:ssss') from
xyz@dblink_test;
output--3051-01-01 00:00:22:2222

Post #1281124

Lowell

Posted Tuesday, April 10, 2012 2:20 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:55 AM
Points: 11,605, Visits: 27,643

milliseconds requires systimestamp, and ss is seconds no matter how many times you repeat it in a string...it's a format mask. you mean to use something like FF2, FF3, FF6 or maybe FF9.

to_char(systimestamp, 'HH24:MI:SS.FF3') FROM dual; is going to get rounded unless you are using the new datetime2 datatypes in SQL 2008+
16:18:53.124 for example, will not transalate 100% as the minimim milliseconds must end in 0,3 or 7 as mentioned before.

SELECT 
to_char(sysdate, 'HH24:MI:SS'),
 to_char(systimestamp, 'HH24:MI:SS.FF6') 
FROM dual;

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1281133

PaulB-TheOneAndOnly

Posted Tuesday, April 10, 2012 2:42 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:03 AM
Points: 2,979, Visits: 4,389

The proper Oracle datatype would be TIMESTAMP not DATE, also you should use TO_TIMESTAMP() not TO_DATE() for conversion.

_____________________________________
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.

Post #1281151

« Prev Topic | Next Topic »

18 posts, Page 1 of 2

1 2 »»

Permissions