Error Converting Data Type Dbtype_dbdate To Datetime
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 error converting data type dbtype_dbtime to datetime site About Us Learn more about Stack Overflow the company Business Learn error converting data type dbtype_dbtime to time more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join 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 minute: Sign up Error converting dbtype_dbdate to date up vote 1 down vote favorite I have an ODBC data source set up as a linked server in MS SQL Server 2012. Throughout this database, most (but not all) columns whose data type is date give me trouble whenever I run queries. I do not have any trouble querying any columns other than these date type columns. The following query: SELECT * FROM OPENQUERY(LINKEDSERVERNAME, 'SELECT Product, DateLastReceipt FROM ProductTable') Gives the following error message in SQL Server Management Studio (2012): Msg 8114, Level 16, State 10, Line 1. Error converting data type DBTYPE_DBDATE to date. I have tried using CONVERT to convert the datatype to varchar per the following discussion without any luck (same error message): http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/3a7d94ee-46a0-40ce-ae95-127ec462fbff Now, there are some columns of the exact same data type (date) that do not give me any trouble at all. In my ProductTable, for example, there is a column called AddedToFile that shows the date that the record was created. This column gives me no trouble at all. Any help someone could offer would be MUCH appreciated. Thanks. sql database odbc sql-server-2012 share|improve this question edited Jul 14 '12 at 22:12 Sir Crispalot 3,74812046 asked Jul 14 '12 at 22:09 Kyle 816 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote hope this helps (i'm not using SQL Server 2012). if your ODBC links to db2, for date range not supported e.g. '0001-01-01', you need to cast. Normally this works. SELECT * FROM OPENQUERY(LINKEDSERVERNAME, 'SELE
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: Error converting data type SQL Server > Transact-SQL Question 0 Sign in to vote I've seen this error mentioned what seems like a million times when I do searches, but I can't figure out the problem. The entire error I get is this: Error converting data type DBTYPE_DBDATE to datetime. I've got a linked server that I'm trying to run a http://stackoverflow.com/questions/11487745/error-converting-dbtype-dbdate-to-date query against: SELECT * From OPENQUERY(linked_server, 'SELECT SomeDate FROM table1') The linked server is an old Unix database with what appears to be a very limited ODBC driver. The problem is that "SomeDate" in many cases is 0000-00-00. If I try to put a cast or a convert in the openquery sql I get an error (same https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3a7d94ee-46a0-40ce-ae95-127ec462fbff/error-converting-data-type?forum=transactsql with a case statement). I haven't found any documentation either. Any thoughts on how I can work around this? I sincerely appreciate any help. Thanks, Nick Thursday, July 29, 2010 4:49 PM Reply | Quote Answers 0 Sign in to vote Found the documentation that led me in the correct direction. Had to do this: {fn convert(SomeDate, SQL_VARCHAR)} as SomeDate Thanks for the help everyone. Proposed as answer by Naomi NModerator Friday, July 30, 2010 6:10 PM Marked as answer by nickfinity Wednesday, August 04, 2010 7:58 PM Friday, July 30, 2010 1:53 PM Reply | Quote All replies 0 Sign in to vote Do you know if Unix database allows any conversion? SELECT * From OPENQUERY(linked_server, 'SELECT cast(SomeDate as char(10)) as SomeDate FROM table1') Premature optimization is the root of all evil in programming. (c) by Donald Knuth Naomi Nosonovsky, Sr. Programmer-Analyst My blog Thursday, July 29, 2010 4:57 PM Reply | Quote Moderator 0 Sign in to vote Can you just return the
Join INTELLIGENT WORK FORUMSFOR COMPUTER PROFESSIONALS Log In Come Join Us! Are you aComputer / IT professional?Join Tek-Tips Forums! Talk With Other Members Be Notified Of ResponsesTo Your Posts Keyword Search One-Click Access To YourFavorite Forums http://www.tek-tips.com/viewthread.cfm?qid=1087756 Automated SignaturesOn Your Posts Best Of All, It's Free! Join Us! *Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting Guidelines Promoting, selling, recruiting, coursework and thesis posting is forbidden.Tek-Tips Posting Policies Jobs Jobs from Indeed What: Where: jobs by Link To This Forum! Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.Just copy and paste the BBCode error converting HTML Markdown MediaWiki reStructuredText code below into your site. Microsoft SQL Server: Programming Forum at Tek-Tips HomeForumsProgrammersDBMS PackagesMicrosoft SQL Server: Programming Forum Error converting data type DBTYPE_DBDATE to datetime thread183-1087756 Forum Search FAQs Links MVPs Error converting data type DBTYPE_DBDATE to datetime Error converting data type DBTYPE_DBDATE to datetime rwaldron (MIS) (OP) 6 Jul 05 10:41 Hi all,I posted something similar a couple of weeks ago but still no joy...I error converting data have a linked server in SQL to Pervase DB.When I run a distributed select * statement to the linked server, it fails when the results reach the first datetime record that contains a 0 (Zero).The pervasive DB design contains some data types of 'date' and they are to be allowed contain a valid date or a zero.I cannot change the design of this DB.I know its the zero in the date field causing problems because When I run a select top3493 on the DB I get back the date row ok...As soon as I go one higher 3494. I get the error "Error converting data type DBTYPE_DBDATE to datetime" Is there some syntax to check that the data is a real date and if not set to Null.I know there is an 'isdate' function but I am not sure of the syntax..Here is an example SELECT top 3493 *From OPENQUERY(sbase_local, 'select date_booked from jobs')retuns 3493 columns of 2002-02-21 00:00:00.000if I use SELECT top 3494 *From OPENQUERY(sbase_local, 'select date_booked from jobs')I get the "Error converting data type DBTYPE_DBDATE to datetime" The record 3494 is the first record the query hits where date_booked contains a zero and not a date.Any Help appretiatedRay RE: Error converting data type DBTYPE_DBDATE to datetime jb