Reading microseconds from Sql server into oracle 10g

Question

Reading microseconds from Sql server into oracle 10g

caramelkaps

SSC-Addicted

Points: 414
More actions
April 10, 2012 at 12:52 pm

#259738

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.

Viewing 15 posts - 1 through 15 (of 17 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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.

caramelkaps SSC-Addicted Points: 414 More actions · Answer 2

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 SSC Guru Points: 442467 More actions · Answer 3

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?

caramelkaps SSC-Addicted Points: 414 More actions · Answer 4

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 SSC Guru Points: 442467 More actions · Answer 5

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

Lowell SSC Guru Points: 323504 More actions · Answer 6

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 SSC-Addicted Points: 414 More actions · Answer 7

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 SSC Guru Points: 323504 More actions · Answer 8

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 SSC Guru Points: 66772 More actions · Answer 9

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.

caramelkaps SSC-Addicted Points: 414 More actions · Answer 10

Thanks...the example u provided works in oracle.

But when i try to exceute this query to get values from sql server table it fails and gives an error date format not recognized.

Query :

select to_char(source_id,'yyyy-mm-dd hh24:mi:ss:ff6') from xyz@dblink_test;--this fails to execute

Lowell SSC Guru Points: 323504 More actions · Answer 11

caramelkaps (4/10/2012)
Thanks...the example u provided works in oracle.
But when i try to exceute this query to get values from sql server table it fails and gives an error date format not recognized.
Query :
select to_char(source_id,'yyyy-mm-dd hh24:mi:ss:ff6') from xyz@dblink_test;--this fails to execute

Paul mentioned the datatype issue for timestamp vs date, and i will again:

what is the datatype in oracle for the column source_id

if source_id is not timestamp, you cannot use the format mask featuring ff6, because that level of preceision requires the timestamp datatype , not date; maybe you can convert date to timestamp before you try to TO_CHAR it?

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 SSC-Addicted Points: 414 More actions · Answer 12

currently im not trying to insert into Oracle.In oracle for insertion I have the column as Timestamp(9).

Currently Im just trying to select from Sql server via Oracle. and I am unable to retrieve the milliseconds and see that in oracle.

source_id is a date column in sql server.

Lowell SSC Guru Points: 323504 More actions · Answer 13

caramelkaps (4/10/2012)
currently im not trying to insert into Oracle.In oracle for insertion I have the column as Timestamp(9).
Currently Im just trying to select from Sql server via Oracle. and I am unable to retrieve the milliseconds and see that in oracle.
source_id is a date column in sql server.

if the column source_id is a date column in SQL server,

SELECT CONVERT(VARCHAR(35),source_id,121) FROM YOURTABLE

will give a result like

"2012-04-10 17:23:19.360"

and no need to fiddle with Oracle...is that what you want?

or are you converting the value from SQL to Oracle?

show us the command you are running (linked server?)

i'm getting a little lost now.

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 SSC-Addicted Points: 414 More actions · Answer 14

When I run this command

SELECT CONVERT(VARCHAR(35),source_id,121) FROM test@dblink_test

I get the following error:ORA-00936: missing expression....hence I am not even able to issue this command..

I am running through Toad.I have a dblink created in Oracle 10g which connects to remote third party SQL Server 2008.The dblink uses HS ODBC connection which uses SQL SERVER native client driver version 2009...Im not sure if conversion over ODBC could be an issue.

I tried convert , CAST , To timestamp....

select cast(source_id as timestamp) from test@dblink_test---gives me an output :1/1/1753 12:00:00.000000 AM....the milliseconds just default to 0000000s.

Thanks.