Error Converting Data Type Dbtype Dbtimestamp To Datetime Sql Server
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting error converting data type dbtype_dbtimestamp to datetime oracle linked server ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join error converting data type dbtype_dbtimestamp to datetime. openquery the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a
Error Converting Data Type Dbtype_dbtimestamp To Datetime. Access
minute: Sign up Linked SQL Server has error with converting data type DBTYPE_DBTIMESTAMP up vote 6 down vote favorite I have a SQL Server 2005 that has a 'Linked Server' to another SQL Server 2005. When I run a simple
Error Converting Data Type (null) To Datetime.
query on one of the tables against the linked server: SELECT TOP 10 [Pat_Id] FROM [Prod].[PIS].[dbo].[W_PATIENT] This error occurs: Msg 8114, Level 16, State 8, Line 1 Error converting data type DBTYPE_DBTIMESTAMP to datetime. Msg 8114, Level 16, State 8, Line 1 Error converting data type DBTYPE_DBTIMESTAMP to datetime. However, when I use OPENQUERY it works: SELECT * FROM OPENQUERY([Prod], 'SELECT TOP 10 [Pat_Id] FROM [PIS].[dbo].[W_PATIENT]') The W_PATIENT table does not have any 'TIMESTAMP' typed fields. It does have 5 DATETIME fields. msg 8114, level 16, state 8, line 1 It is also import to note that I can query other tables with DATETIME values without an problem. The problem is focused on the W_PATIENT table. sql-server-2005 share|improve this question asked Nov 18 '09 at 21:54 jacksonakj 6391819 Have you tried casting [Pat_Id] to binary(8)? That is equivalent to TIMESTAMP and it would be interesting to see if that worked. –ajdams Nov 18 '09 at 22:50 Are you sure your linked server is set up correctly? What does this query yield: SELECT product, provider FROM sys.servers WHERE name = 'Prod'; –Aaron Bertrand Nov 19 '09 at 2:06 I believe I have setup the linked server correctly, because I am able to query other tables in the same database on the linked server. Here is the result of the query. product provider ----------- --------- SQL Server SQLNCLI –jacksonakj Nov 19 '09 at 16:38 Good thought. Before your comment I tried casting the DATETIME fields to TIMESTAMP and that worked if I was connected locally to the Prod database. I just used BINARY(8) as suggested that also worked. I was hoping that by casting each of the DATETIME individually I would be able to identify the one with bad data, but no luck. –jacksonakj Nov 19 '09 at 16:47 And you're sure W_Patient is a table, and not a view? –Aaron Bertrand Nov 19 '09 at 16:59 add a comment| 1 Answer 1 active oldest votes
type conversions Linked server queries and OPENQUERY differ in where a query is processed.
Sql Server Openquery
This is of particular concern when bringing data from Oracle to sql server convert SQL Server because of data type conversion issues. Here's a query that can fail when converted to a SQL Server linked server query: SELECT ColA, ColB, ColC, ColD FROM OraSchema.OraTable; Written as a linked server query issued from SQL Server, it looks like this: SELECT ColA, ColB, http://stackoverflow.com/questions/1759356/linked-sql-server-has-error-with-converting-data-type-dbtype-dbtimestamp Col, ColD FROM OraLink..OraSchema.OraTable; In most cases, the linked server query will work just fine. That's because dates that are valid for business are usually valid for Oracle DATE columns and SQL Server DATETIME columns. The problem occurs when there are Oracle dates prior to January 1, 1753 A.D., which is the earliest date allowed in a SQL Server http://sqlblog.com/blogs/john_paul_cook/archive/2009/07/31/linked-server-vs-openquery-for-handling-data-type-conversions.aspx DATETIME column. Msg 8114, Level 16, State 8, Line 1Error converting data type DBTYPE_DBTIMESTAMP to datetime. Don't assume that because your business started a long time after January 1, 1753 that you don't have a problem. I've seen plenty of Oracle dates earlier than that caused by applications that failed to do proper input validation. A Oracle CASE statement similar to one of these can be used to filter out dates that are invalid for SQL Server: CASE WHEN ColC < TO_DATE('17530101','YYYYMMDD') THEN NULL ELSE ColC END CASE WHEN ColC < TO_DATE('17530101','YYYYMMDD') THEN TO_DATE('99991231','YYYYMMDD') ELSE ColC END But TO_DATE is an Oracle function, so it can't be incorporated in a linked server query because the linked server query is issued from SQL Server: SELECT CASE WHEN ColC < TO_DATE('17530101','YYYYMMDD') THEN TO_DATE('99991231','YYYYMMDD') ELSE ColC ENDFROM OraLink..OraSchema.OraTable; Msg 195, Level 15, State 10, Line 1 'TO_DATE' is not a recognized built-in function name. Even if such syntax could be used, the query would still fail with the data type conver
for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time https://www.experts-exchange.com/questions/21535538/Error-converting-data-type-DBTYPE-DBTIMESTAMP-to-datetime.html Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > Error converting data type DBTYPE_DBTIMESTAMP to datetime Want to Advertise Here? Solved Error converting data type error converting DBTYPE_DBTIMESTAMP to datetime Posted on 2005-08-22 MS SQL Server 1 Verified Solution 13 Comments 4,372 Views Last Modified: 2012-06-27 Hi Experts, I'm trying to do a distributed query within SQL Server 2000 (querying an Oracle db) to select candidate, candidate source and the date of the latest candidate source entry: SELECT MNME.NM_LTNM as candidate, error converting data LSRC.SR_DESC AS source, MAX(MSRCHIST.SRCH_DATE) AS [source date] FROM EZ..EZLIVE.MNME MNME INNER JOIN EZ..EZLIVE.MSRCHIST MSRCHIST ON MNME.NM_IDNO = MSRCHIST.SRCH_NM_NM INNER JOIN EZ..EZLIVE.LSRC LSRC ON MSRCHIST.SRCH_SOURCE_SR = LSRC.SR_IDNO WHERE (MNME.NM_RUPD >= CONVERT(DATETIME, '2005-08-22 00:00:00', 102)) GROUP BY MNME.NM_LTNM, LSRC.SR_DESC I get the following error on the MAX(MSRCHIST.SRCH_DATE) AS [source date] line: Error converting data type DBTYPE_DBTIMESTAMP to datetime. Any help much appreciated! 0 Question by:proltdman Facebook Twitter LinkedIn Google LVL 28 Best Solution byrafrancisco I guess there's a certain record in the table that SQL cannot convert to a datetime data type. Will the following statement give you any error? SELECT SRCH_DATE AS [source date] FROM Go to Solution 13 Comments LVL 14 Overall: Level 14 MS SQL Server 12 Message Expert Comment by:mherchl2005-08-22 try somthing like this: SELECT MNME.NM_LTNM as candidate, LSRC.SR_DESC AS source, MAX(case when MSRCHIST.SRCH_DATE < '19000101' then 19000101 wh