COLLATE Errors

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

     

     

     

  • 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?

  • 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 month, 3 weeks ago by  saintor1.
    • This reply was modified 1 month, 3 weeks ago by  saintor1.
  • 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.

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

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