Skip to content
Advertisement

Conversion failed error when running case statement

I’m getting an error ‘conversion failed when converting the varchar value ‘NO’ to data type int’ when I run the following case statement. Can someone tell me what I am doing wrong here? Thanks

,CASE
         WHEN DATEDIFF(DAY, convert(VARCHAR, Deliveries.DUE_DATE, 10), convert(VARCHAR, DELIVERIES.OB_DELIVERY_DATE, 10)) > 0
                THEN DATEDIFF(DAY, convert(VARCHAR, Deliveries.DUE_DATE, 10), convert(VARCHAR, DELIVERIES.OB_DELIVERY_DATE, 10))
         WHEN DATEDIFF(DAY, convert(VARCHAR, Deliveries.DUE_DATE, 10), convert(VARCHAR, DELIVERIES.OB_DELIVERY_DATE, 10)) < 0
                THEN DATEDIFF(DAY, convert(VARCHAR, Deliveries.DUE_DATE, 10), convert(VARCHAR, DELIVERIES.OB_DELIVERY_DATE, 10))

          ELSE 'NO'

       END AS DAYS_BEFORE_DUE_DATE

Advertisement

Answer

In this case you can covert the result of the DATEDIFF function like this:

select CASE WHEN DATEDIFF(DAY, convert(VARCHAR, Deliveries.DUE_DATE, 10), convert(VARCHAR, DELIVERIES.OB_DELIVERY_DATE, 10)) > 0
            THEN convert(varchar, DATEDIFF(DAY, convert(VARCHAR, Deliveries.DUE_DATE, 10), convert(VARCHAR, DELIVERIES.OB_DELIVERY_DATE, 10)))
            WHEN DATEDIFF(DAY, convert(VARCHAR, Deliveries.DUE_DATE, 10), convert(VARCHAR, DELIVERIES.OB_DELIVERY_DATE, 10)) < 0
            THEN convert(varchar, DATEDIFF(DAY, convert(VARCHAR, Deliveries.DUE_DATE, 10), convert(VARCHAR, DELIVERIES.OB_DELIVERY_DATE, 10)))
            ELSE 'NO'
       END AS DAYS_BEFORE_DUE_DATE
from Deliveries    

Here is a demo

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement