Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nickfinity
Yak Posting Veteran
55 Posts |
 Posted - 2010-07-29 : 00:32:29
|
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 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 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 |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-07-29 : 00:41:33
|
| Did your try converting to Varchar?Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
 |
|
|
nickfinity
Yak Posting Veteran
55 Posts |
Posted - 2010-07-29 : 10:03:07
|
Thanks for the help. How would I convert to a varchar? If I try anything but very basic SQL in the OPENQUERY I get errors. I also tried SELECT CONVERT(varchar(10),SomeDate ) From OPENQUERY(linked_server, 'SELECT SomeDate FROM table1') I get the same error though. Thanks again for the help. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-07-30 : 01:01:22
|
| No you will have to convert the datatype in the select statement.I mean use the inbuilt convert function to varchar of the Unix database .SELECT * From OPENQUERY(linked_server, 'SELECT To_Char(SomeDate) FROM table1')Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
|
|
|
nickfinity
Yak Posting Veteran
55 Posts |
Posted - 2010-07-30 : 11:18:38
|
| Thanks again for the help. I was able to find some documentation. Turns out the syntax was:SELECT * From OPENQUERY(linked_server, 'SELECT {fn convert(SomeDate, SQL_VARCHAR)} as SomeDate FROM table1') |
|
|
|
|
|
|
Error converting data type