将dbtype_dbdate转换为日期时出错 [英] Error converting dbtype_dbdate to date

问题描述

我在MS SQL Server 2012中将ODBC数据源设置为链接服务器.在整个数据库中，大多数(但不是全部)数据类型为date的列都会在我运行查询时给我带来麻烦.查询这些日期类型列以外的任何列都没有任何麻烦.

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.

以下查询:

SELECT *
FROM   OPENQUERY(LINKEDSERVERNAME, 'SELECT Product, 
                                           DateLastReceipt
                                    FROM   ProductTable')

在SQL Server Management Studio(2012)中给出以下错误消息:

Gives the following error message in SQL Server Management Studio (2012):

消息8114，级别16，状态10，第1行.将数据类型DBTYPE_DBDATE转换为日期时出错.

Msg 8114, Level 16, State 10, Line 1. Error converting data type DBTYPE_DBDATE to date.

我尝试按照以下讨论使用CONVERT将数据类型转换为varchar，但没有任何运气(相同的错误消息):

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

现在，有些列具有完全相同的数据类型(日期)，这些列完全不会给我带来任何麻烦.例如，在我的ProductTable中，有一列称为AddedToFile，它显示了记录的创建日期.本专栏完全不会给我带来任何麻烦.

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.

谢谢.

推荐答案

希望这会有所帮助(我不使用SQL Server 2012).
如果您的ODBC链接到db2，则日期范围不受支持，例如'0001-01-01'，则需要投射.通常这是可行的.

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')

如果您仍然希望将结果作为日期，只需使用CASE并将无效日期替换为默认日期即可.

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')

您可能需要将CURRENT_DATE更改为CURRENT_TIMESTAMP，并且CASE的组成取决于您的数据库服务器和您的要求

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

这篇关于将dbtype_dbdate转换为日期时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！