I have a question. I keep getting an error
CASE WHEN j.PHPPDT = '00010101' THEN '0' ELSE j.PHPPDT END AS NEWPPDT,
CASE WHEN j.PHPDTE = '00010101' THEN '0' ELSE j.PHPDTE END AS NEWPROMDT
Columns j.PHPPDT and j.PHPDTE are of date type.
When I run just them, the code seems to run. However, later with my code I create another CTE and need to select them and then I get a mistake
NEWPPDT AS PrintersPortDate,
NEWPROMDT AS PromiseByDate,
The mistake says
Conversion failed when converting date and/or time from character string.
I am not really sure where and how to fix it, because normally when such things happen I just use WHERE CONVERT(DATE, CONVERT(CHAR(8), NEWPPDT )) but here it doesn’t help. Further, I am not sure I understand where to even insert it. Should I insert it to j.PHPPDT in my case statement or when I select the column…
It is pretty confusing. Does someone know what might help?
>Solution :
You can try using some default date value instead of ‘0’
CASE WHEN j.PHPPDT = '00010101' THEN '12/31/2099' ELSE j.PHPPDT END AS NEWPPDT,
CASE WHEN j.PHPDTE = '00010101' THEN '12/31/2099' ELSE j.PHPDTE END AS NEWPROMDT