Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Reading microseconds from Sql server into oracle 10g Expand / Collapse
Author
Message
Posted Tuesday, April 10, 2012 12:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, April 10, 2012 1:02 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:12 PM
Points: 23,397, Visits: 32,241
caramelkaps (4/10/2012)
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.


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



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
Posted Tuesday, April 10, 2012 1:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, April 10, 2012 1:12 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:12 PM
Points: 23,397, Visits: 32,241
caramelkaps (4/10/2012)
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.



How are you pulling the data over?



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 #1281086
Posted Tuesday, April 10, 2012 1:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, April 10, 2012 1:24 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:12 PM
Points: 23,397, Visits: 32,241
Out of my depth. Oracle experience is light and I didn't have to pull data from a SQL Server database over a DBLINK.



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 #1281102
Posted Tuesday, April 10, 2012 1:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 12,916, Visits: 32,080
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
Posted Tuesday, April 10, 2012 2:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, April 10, 2012 2:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 12,916, Visits: 32,080
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
Posted Tuesday, April 10, 2012 2:42 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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 »

Add to briefcase 12»»

Permissions Expand / Collapse