System date is displaying when the value is NULL in date field in SSRS report?

Question

Below is my SQL query used in SSRS report

SELECT Claimname,CONVERT(VARCHAR,ClaimResponsedate,101) FROM Claim WHERE DataSourceID = 100011

From the above query 'ClaimResponsedate' may get NULL values but in SSRS report for this field displaying 1/1/1990 when the value comes as NULL.

I need to display empty value in report if the date value is NULL.

I tried below code to avoid but it is not working =IIF(IsNothing(Fields!ClaimResponsedate.Value),"",Fields!ClaimResponsedate.Value) OR =IIF(Len(Fields!ClaimResponsedate)=0,"",Fields!ClaimResponsedate.value)

So please help me to achieve this i.e. display empty value in report if the date value is NULL

Thanks in advance

Answer 1

Change your SQL to be:

SELECT Claimname,
    CASE WHEN ClaimResponseDate IS NULL THEN NULL ELSE 
    CONVERT(VARCHAR,ClaimResponsedate,101) END as [ClaimResponseDate]
 FROM Claim WHERE DataSourceID = 100011

---

The other option is to change your report field to be the following:

=Replace(Fields!ClaimResponsedate.Value,"1/1/1900","")