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