InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 00:59:01 GMT20http://www.sqlservercentral.com/Forums/Topic1281060-1042-1.aspxThanks everbody for all the insight.I have posted my issue in oracle forum as well and am yet to receive any reply.Unfortunately we have no control over the third party sql server DB.So might not be able to request them to either change all the columns to varchar or create additional views.Will update here on the progress.Thanks.Wed, 11 Apr 2012 10:42:23 GMTcaramelkapshttp://www.sqlservercentral.com/Forums/Topic1281060-1042-1.aspx[quote][b]Lowell (4/11/2012)[/b]this is actually an Oracle question, and dblink behavior[/quote]Oracle DBLink connections behave like a normal client connection meaning that the server on the remote side of the DBLink perceives the connection as a client - nothing more, nothing else.My guess is that poster might be mixing sql syntax sometimes hitting the remote SQL Server either with PL/SQL or Oracle SQL syntax which wouldn't fly.I think that the solution pointed by Lowell, creating a view on SQL Server side casting the column as varchar() then do whatever you want with it on Oracle side.Wed, 11 Apr 2012 09:19:55 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic1281060-1042-1.aspxthat was my point.I thought you said you said sourceid exists on[b] SQL Server.[/b], and assumed you were doing the work from SQL server, with linked servers to Oracle.What confused me was [b] test@dblink_test[/b] I think you are saying that is a dblink TO SQL Server? so your commands are all originating on the Oracle side, not SQL Server, correct?What Table does source_id exist in? i would be lazy, and on SQL Server,create a view that formats the the source_id to CONVERT(VARCHAR(35),source_id,121) this is actually an Oracle question, and dblink behavior, and not really a SQL question, which is causing all the confusion...you ar egetting SQL-oriented answers from us.see this post, where they specifically say you need to cast it to a char to get timestamps:[url]https://forums.oracle.com/forums/thread.jspa?threadID=2359176[/url]Wed, 11 Apr 2012 08:56:51 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1281060-1042-1.aspxWhen I run this command SELECT CONVERT(VARCHAR(35),source_id,121) FROM test@dblink_testI 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.Wed, 11 Apr 2012 08:40:49 GMTcaramelkapshttp://www.sqlservercentral.com/Forums/Topic1281060-1042-1.aspx[quote][b]caramelkaps (4/10/2012)[/b][hr]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.[/quote]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.Tue, 10 Apr 2012 15:25:45 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1281060-1042-1.aspxcurrently 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.Tue, 10 Apr 2012 15:16:00 GMTcaramelkapshttp://www.sqlservercentral.com/Forums/Topic1281060-1042-1.aspx[quote][b]caramelkaps (4/10/2012)[/b][hr]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[/quote]Paul mentioned the datatype issue for timestamp vs date, and i will again:what is the datatype in oracle for the column [b]source_id[/b]if source_id is not timestamp, you cannot use the format mask featuring [b]ff6[/b], 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?Tue, 10 Apr 2012 15:01:50 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1281060-1042-1.aspxThanks...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 executeTue, 10 Apr 2012 14:43:51 GMTcaramelkapshttp://www.sqlservercentral.com/Forums/Topic1281060-1042-1.aspxThe proper Oracle datatype would be TIMESTAMP not DATE, also you should use TO_TIMESTAMP() not TO_DATE() for conversion.Tue, 10 Apr 2012 14:42:42 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic1281060-1042-1.aspxmilliseconds 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.[code]SELECT to_char(sysdate, 'HH24:MI:SS'), to_char(systimestamp, 'HH24:MI:SS.FF6') FROM dual;[/code]Tue, 10 Apr 2012 14:20:37 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1281060-1042-1.aspxselect to_char(sysdate,'mm/dd/yyyy hh24:mi:ss.FF') from dual----this gives me an error date format not recognized in oracleselect to_char(sysdate,'mm/dd/yyyy hh24:mi:ss.ssss') from dualgives me 04/10/2012 16:00:36.3636Note that milliseconds is just a repeat of secondsIf I try this from oracle to sql server--gives me an error : format code appears twiceselect 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 secondsselect to_char(source_id,'yyyy-mm-dd hh24:mi:ss:ssss') from xyz@dblink_test;output--3051-01-01 00:00:22:2222Tue, 10 Apr 2012 14:05:27 GMTcaramelkapshttp://www.sqlservercentral.com/Forums/Topic1281060-1042-1.aspxare you using the Oracle TO_DATE() function to convert the data?what is your string format for the conversion?[b]edit[/b]jsut read you need to use TO_TIMESTAMP() in oracle to get fractions of seconds:[code]SELECT TO_TIMESTAMP('2004-09-30 23:53:48,140000000', 'YYYY-MM-DD HH24:MI:SS,FF9') FROM dual [/code][b][url]http://stackoverflow.com/questions/1758219/string-to-date-in-oracle-with-milliseconds[/url][/b]Tue, 10 Apr 2012 13:36:48 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1281060-1042-1.aspxOut of my depth. Oracle experience is light and I didn't have to pull data from a SQL Server database over a DBLINK.Tue, 10 Apr 2012 13:24:55 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1281060-1042-1.aspxThrough an ODBC Connection.Sql Server -2008Using SQL SERVER Native client 10.0 driver version 2009.100.1600.01Then created a dblink in oracle to use the ODBC connection to get the data.We have Oracle 10g.Tue, 10 Apr 2012 13:20:38 GMTcaramelkapshttp://www.sqlservercentral.com/Forums/Topic1281060-1042-1.aspx[quote][b]caramelkaps (4/10/2012)[/b][hr]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 egsql server Oraclesource_id source id01/01/2012 6:30:45:346 01/01/2012 6:30:45:4545 .Thanks.[/quote]How are you pulling the data over?Tue, 10 Apr 2012 13:12:21 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1281060-1042-1.aspxI 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 egsql server Oraclesource_id source id01/01/2012 6:30:45:346 01/01/2012 6:30:45:4545 .Thanks.Tue, 10 Apr 2012 13:08:47 GMTcaramelkapshttp://www.sqlservercentral.com/Forums/Topic1281060-1042-1.aspx[quote][b]caramelkaps (4/10/2012)[/b][hr]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 id01/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.[/quote]SQL Server's datetime data type only records time down to the milliseconds, and then only to .000, .003, and .007.Tue, 10 Apr 2012 13:02:15 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1281060-1042-1.aspxHi,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 id01/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.Tue, 10 Apr 2012 12:52:54 GMTcaramelkaps