SQL Server 2005日期比较 - 排序规则问题

In my DB, I have a table that was created from an Excel sheet, via a Linked Server option.

在我的数据库中,我有一个通过链接服务器选项从Excel工作表创建的表。

I am now trying to compare its contents versus one of my main tables.

我现在正在尝试将其内容与我的主要表格进行比较。

The tables I am comparing are in the same database.

我正在比较的表位于同一个数据库中。

There is a date column in both tables, both types are datetime and have Collation of SQL_Latin1_General_CP1_CI_AS, the same as the DB.

两个表中都有一个日期列,两个类型都是datetime,并且具有SQL_Latin1_General_CP1_CI_AS的排序规则,与DB相同。

The server collation is Latin1_General_CI_AS

服务器排序规则是Latin1_General_CI_AS

However when I try to run a query comparing the dates between the tables, I get the error:

但是,当我尝试运行比较表之间的日期的查询时,我收到错误:

      Cannot resolve the collation conflict between
      "Latin1_General_CI_AS" and
      "SQL_Latin1_General_CP1_CI_AS" in the
      equal to operation.

I have tried with and without the COLLATE option, using both collation settings.

我已尝试使用和不使用COLLATE选项,使用两种排序规则设置。

My query is:

我的查询是:

select * , hxl.holiday_dt, 
datediff(d, h.holiday_dt collate SQL_Latin1_General_CP1_CI_AS, 
            hxl.holiday_dt collate SQL_Latin1_General_CP1_CI_AS)
from holiday h, Holiday_XL hxl
where h.currency_cd=hxl.currency_cd

In fact any query involving both tables gives exactly the same collation error, eg this one:

实际上,涉及两个表的任何查询都会产生完全相同的排序规则错误,例如:

select count(*)
from Holiday_XL c
where c.currency_cd in (select distinct h.currency_cd from holiday h)

Thanks in advance for any thoughts.

提前感谢任何想法。

Regards, Chris

2 个解决方案

#1

The error is being reported on the currency comparison h.currency_cd=hxl.currency_cd, not on datediff, so try force collation on the currencies.

在货币比较h.currency_cd = hxl.currency_cd上报告错误,而不是在datediff上报告错误,因此请尝试强制对货币进行整理。

Collation is only relevant for character (char, varchar, nvarchar) and text types.

排序规则仅与字符(char,varchar,nvarchar)和文本类型相关。

#2

Under the Collation and give the collation table level there are lot of tips and code available in net search with topic of collation.

在排序规则下并给出排序表级别,在网络搜索中有很多提示和代码可用于排序主题。

Still if you have problem. Insert the content of the excel in to @table or permenant table and provide Collation to that table while selecting for comparison

如果你有问题,将excel的内容插入@table或permenant表,并在选择进行比较时为该表提供Collation

#1