获取错误不允许从数据类型datetime到int的隐式转换。使用CONVERT函数运行此查询。在SQL Server中

I am new here in sql server, I am working on query, when i run the query it gives me error Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query., i tried all the possible scenarios, still i am not able to resolve it, here is my sql query, can anyone please point to me why i am getting this error ?

我在sql server中是新手，我正在进行查询，当我运行查询时它给了我错误不允许从数据类型datetime到int的隐式转换。使用CONVERT函数运行此查询。，我尝试了所有可能的方案，仍然我无法解决它，这是我的SQL查询，任何人都可以请指向我为什么我收到此错误？

UPDATE [e]
    SET 
       [e].[LUPADays] = IIF((IIF(([p].[payer_type]) <> '1', 0, IIF([EpEnd] <= GETDATE(), 0, IIF([TotVisits] < 5, 1, 0)))) = 0, 0, CONVERT(datetime,EpEnd) - GETDATE())
FROM [tb_Episode] AS [e]
LEFT JOIN [tb_Payer] AS [p]
     ON([e].[CustID] = [p].[company_id])
       AND ([e].[PayorType] = [p].[payor_type])
LEFT JOIN [tb_HHPPS] AS [h]
     ON [e].[HHPPS] = [h].[HHPPS]
WHERE 
    [e].[billed_flag] = '0';

3 个解决方案

#1

From your own query, the error appears to be that you are comparing EpEnd against GETDATE() directly, without a conversion. You do make the appropriate conversion later in the query, so just do it earlier as well:

从您自己的查询中，错误似乎是您正在直接比较EpEnd与GETDATE（），而不进行转换。您确实在稍后的查询中进行了适当的转换，所以也可以在之前进行：

UPDATE [e]
SET 
   [e].[LUPADays] = IIF((IIF([p].[payer_type] <> '1', 0,
       IIF(CONVERT(datetime, EpEnd) <= GETDATE(), 0, IIF([TotVisits] < 5, 1, 0)))) = 0, 0, CONVERT(datetime, EpEnd) - GETDATE())
                  -- ^^^ change here
FROM [tb_Episode] AS [e]
LEFT JOIN [tb_Payer] AS [p]
     ON([e].[CustID] = [p].[company_id])
       AND ([e].[PayorType] = [p].[payor_type])
LEFT JOIN [tb_HHPPS] AS [h]
     ON [e].[HHPPS] = [h].[HHPPS]
WHERE 
    [e].[billed_flag] = '0';

By the way, the logic in your SET clause looks convoluted. There is probably a way to simplify that as well.

顺便说一句，SET子句中的逻辑看起来很复杂。也许有一种方法可以简化它。

Edit:

编辑：

If, after making appropriate conversions for all datetime columns, you are still getting a conversion error, then I suggest that you have bad data somewhere in that column. This sort of error is one of the reasons why you should not be storing date information as text.

如果在对所有日期时间列进行适当的转换后，您仍然收到转换错误，那么我建议您在该列的某个位置存在错误数据。这种错误是您不应将日期信息存储为文本的原因之一。

#2

I think your field [e].[LUPADays] is of INT type that's why you get the error when you try to set there your DATETIME value EpEnd - GETDATE(). It should be int.

我认为你的字段[e]。[LUPADays]属于INT类型，这就是当你尝试设置DATETIME值EpEnd - GETDATE（）时出现错误的原因。它应该是int。

 UPDATE [e]
    SET 
        [e].[LUPADays] = CASE 
        WHEN [p].[payer_type] <> '1' 
            OR EpEnd <= DATEDIFF(day, 0, GETDATE()) 
            OR [TotVisits] >= 5 THEN 0
        ELSE EpEnd - DATEDIFF(day, 0, GETDATE())
        END
    FROM [tb_Episode] AS [e]
    LEFT JOIN [tb_Payer] AS [p]
         ON([e].[CustID] = [p].[company_id])
           AND ([e].[PayorType] = [p].[payor_type])
    LEFT JOIN [tb_HHPPS] AS [h]
         ON [e].[HHPPS] = [h].[HHPPS]
    WHERE 
        [e].[billed_flag] = '0';

UPD: It seems that your field EpEnd is neither INT nor DATETIME but maybe VARCHAR, so you should make some conversion here: OR EpEnd <= DATEDIFF(day, 0, GETDATE()) and here EpEnd - DATEDIFF(day, 0, GETDATE()).

UPD：你的字段EpEnd似乎既不是INT也不是DATETIME但可能是VARCHAR，所以你应该在这里做一些转换：OR EpEnd <= DATEDIFF（day，0，GETDATE（））和这里EpEnd - DATEDIFF（day，0，GETDATE）（））。

#3

I assume the column [e].[LUPADays] is an int. The result of your IFF's is either 0, or CONVERT(datetime,EpEnd) - GETDATE(). This last expression results in a datetime, which sql server cannot implicitly convert to an int. What you are looking for is: SELECT DATEDIFF(DAY, GETDATE(), EpEnd) instead of subtracting datetimes.

我假设列[e]。[LUPADays]是一个int。你的IFF的结果是0或CONVERT（datetime，EpEnd） - GETDATE（）。最后一个表达式导致datetime，sql server无法隐式转换为int。你要找的是：SELECT DATEDIFF（DAY，GETDATE（），EpEnd）而不是减去日期时间。

Also, it helps if you don't add parentheses around everything, only use them where they are needed.

此外，如果您不在所有内容周围添加括号，只会在需要的地方使用它们，这会有所帮助。

#1