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