CODEWITHSUNDEEP
sql-server-2008ssrs-2008reporting-services
VInayK
VInayK

Reputation: 1559

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

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

Upvotes: 1

Views: 3081

Answers (1)

cjk
cjk

Reputation: 46465

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","")

Upvotes: 2

Related Questions