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 datatype DATE.

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 error is:

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?

CodePudding user response：

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