Error converting dbtype_dbdate to date

Question

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.

Solution

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, 'SELECT Product, 
                                       CAST(DateLastReceipt AS CHAR(10))
                                FROM   ProductTable')

If you still want the result as date, just use CASE and replace the invalid date to your default date .e.g.

SELECT *
FROM   OPENQUERY(LINKEDSERVERNAME, 'SELECT Product, 
                               ,CASE WHEN DateLastReceipt AS CHAR(10)) = ''0001-01-01''
                                       THEN CURRENT_DATE
                                     ELSE DateLastReceipt 
                                     END
                                FROM   ProductTable')

You might need to change CURRENT_DATE to CURRENT_TIMESTAMP and CASE composition depends on your db server and your requirement

Comments

[Kyle]

Thanks @lamLam. Your first suggested query throws the following error for me: OLE DB provider "MSDASQL" for linked server "LINKEDSERVERNAME" returned message "[Transoft][TSODBC][usqlsd]'FROM' expected here (()". Using the second query, I get the following error: OLE DB provider "MSDASQL" for linked server "LINKEDSERVERNAME" returned message "[Transoft][TSODBC][usqlsd]Name expected (,)". Is my syntax off with both of these? These don't seem to be the most descriptive error messages. Thanks for your help. I'm new to this.

[lamLam]

sorry i never use Transoft stuffs before. You could try...Run the select statement directly using Transoft clients (if any) or directly to the server SELECT Product, CAST(DateLastReceipt AS CHAR(10)) FROM ProductTable. Search Transoft sql manual on converting date to character. Probably Transoft does not support CAST, or using CHARACTER(10) instead of CHAR or it uses different functions. Once work, just copy the whole thing back to SQL server script