Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reading microseconds from Sql server into oracle 10g


Reading microseconds from Sql server into oracle 10g

Author
Message
caramelkaps
caramelkaps
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24146 Visits: 37920
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.

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)
caramelkaps
caramelkaps
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24146 Visits: 37920
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?

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)
caramelkaps
caramelkaps
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24146 Visits: 37920
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

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)
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14887 Visits: 38888
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

caramelkaps
caramelkaps
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14887 Visits: 38888
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3091 Visits: 4639
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search