My original query is given below. even after placing condition 'is null', am supposed to get '2100-12-31' as output. but it's resulting NULL Can any body please help me.
case ty.[ASSC_3PRTY_END_DT]
when NULL THEN '2100-12-31'
ELSE replace(convert(varchar,ty.[ASSC_3PRTY_END_DT], 111),'/','-')
END AS thirdPartyAccessLastDate,
output:
NULL
full query is :
DECLARE @STUDENTID VARCHAR(10)='804403895'
SELECT ty.[STU_ID],
ty.UCLA_3PRTY_ID,
ty.ASSC_3PRTY_DESC,
dr.FIRST_NAME_3PRTY,
dr.LAST_NAME_3PRTY,
dr.EMAIL_ADDR_3PRTY,
replace(convert(varchar,ty.[ASSC_3PRTY_STRT_DT], 111),'/','-') thirdPartyAccessStartDate,
case ty.[ASSC_3PRTY_END_DT]
when NULL THEN '2100-12-31'
ELSE replace(convert(varchar,ty.[ASSC_3PRTY_END_DT], 111),'/','-')
END AS thirdPartyAccessLastDate,
case when ty.[ASSC_3PRTY_END_DT] IS NULL or ty.[ASSC_3PRTY_END_DT] > GETDATE() then 'Y'
else 'N'
end as thirdPartyAccessActive
FROM DBO.SI0_STU_3PRTY ty left join dbo.SI0_3PRTY_DIR dr on ty.UCLA_3PRTY_ID=dr.UCLA_3PRTY_ID
where ty.STU_ID = @STUDENTID