SQL Server 2005 date comparison - collation issues

Question

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

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.

The server collation is 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.

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

Answer 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.

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

Answer 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