sqlsql-servert-sqlvarchar

Conversion failed error when running case expression


I'm getting an error:

conversion failed when converting the varchar value 'NO' to data type int

when I run the following case expression. 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

Solution

  • 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