COLLATE Errors

Question

Post reply

COLLATE Errors

saintor1

Mr or Mrs. 500

Points: 562

December 9, 2022 at 1:15 pm

#4123787

I have been using this for a long time. It was done initially in MS SQL 2008v2 and we are now using 2019

SELECT 
CASE 
    WHEN [Server].dbo.[YearWeek]([W_SE-MFG].[dbo].previousWeekDayDate(Dateadd(d,-21,J.JobDeliveryDate),1)) < [Server].dbo.[YearWeek](GETDATE()) THEN 'PAST' 
    WHEN [Server].dbo.[YearWeek]([W_SE-MFG].[dbo].previousWeekDayDate(Dateadd(d,-21,J.JobDeliveryDate),1)) > [Server].dbo.[YearWeek](DATEADD(MONTH, 3, GETDATE())) THEN 'LATER' 
    ELSE [Server].dbo.[YearWeek]([Server].[dbo].previousWeekDayDate(Dateadd(d,-21,J.JobDeliveryDate),1)) 
END 
AS ReqDate FROM Server.dbo.WipJobAllMat M 
INNER JOIN Server.dbo.WipMaster J ON J.Job=M.Job

The YearWeek function is

ALTER FUNCTION [dbo].[YearWeek] (
  @MyDate as datetime
) RETURNS char(7)
AS
BEGIN
 
 
 DECLARE @Result char(7)
 
 
  SELECT @Result=CAST(DATEPART(yyyy, @MyDate ) AS char(4)) + '-' + RIGHT (CONVERT (varchar, 0) + CONVERT (VARCHAR, DATEPART(wk, @MyDate)), 2) 
 
  RETURN (@Result)
 
END

The previousWeekDayDate function is

ALTER function [dbo].[previousWeekDayDate](@anyDate date, @anyWeekDay int)
returns Date
as
begin
    return DATEADD(dd, ((DATEPART(dw,@anyDate) + @@DateFirst - @anyWeekDay + 13) % 7) * -1, @anyDate)
end

It was working until today. I get the the following message.

"Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "Latin1_General_BIN" and "SQL_Latin1_General_CP850_BIN" in CASE operator."

It is the first time that I am faced to the a COLLATE problem. I tried adding "COLLATE Latin1_General_BIN", then "COLLATE SQL_Latin1_General_CP850_BIN" after the CASE...END, no success. I wonder what happened and how should I address this. TIA.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1328 More actions · Answer 1

When did you move to SQL Server 2019?

Are you getting that error with any/all parameters, or only w/ specific cases? If only some cases, what parameters result in this error?

Did database or table/column collation change?

saintor1 Mr or Mrs. 500 Points: 562 More actions · Answer 2

It was like 3 months ago, but the code has been running since then until today. No known change whatsoever, all COLLATE info in properties is set to "Latin1_General_BIN". How can I find the reference to "SQL_Latin1_General_CP850_BIN" ?

This reply was modified 1 year, 4 months ago by saintor1.
This reply was modified 1 year, 4 months ago by saintor1.

saintor1 Mr or Mrs. 500 Points: 562 More actions · Answer 3

Update; I found a way-around. When I do the same instructions through a store procedure (EXEC) I don't have this error. It is just when using it directly in a query. I also found out that our server default COLLATE is set to SQL_Latin1_General_CP850_BIN but all objects as tables are Latin1_General_BIN.