CODEWITHSUNDEEP
sqlsql-server
Nikul Panchal
Nikul Panchal

Reputation: 711

getting error Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query. in 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 ?

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';

Upvotes: 1

Views: 12406

Answers (3)

HoneyBadger
HoneyBadger

Reputation: 15130

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.

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

Upvotes: 1

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

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.

 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()).

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

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:

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.

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.

Upvotes: 1

Related Questions