Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 SSRS - format date with null entries

Author  Topic 

spward
Starting Member

3 Posts

Posted - 2012-02-07 : 16:45:06
Looking for a clean way to write an expression that will ignore null values in the date field.

Right now, I am using "=FormatDateTime(Fields!Date.Value, DateFormat.ShortDate)" to return 1/15/2012. However, I have other columns that contain "null" values and it returns 1/1/0001 when the expression is applied. Anyone have expereince with this? I have also tried "=IIF(IsNothing(Fields!PromisedByDate),0, (FormatDateTime(Fields!PromisedByDate.Value,DateFormat.ShortDate)))", but it return a value for null items.

Scott

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 20:35:28
"=IIF(IsNothing(Fields!PromisedByDate),"", FormatDateTime(Fields!Date.Value, DateFormat.ShortDate))"

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

spward
Starting Member

3 Posts

Posted - 2012-02-08 : 09:21:58
Thanks for the help with the process, but it still returns a date of 1/1/0001 for the date. Maybe it's an SSRS 2.0 issue.

Scott
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-08 : 14:21:08
instead of using FormatDataTime did you try setting it from format options inside cell properties?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

spward
Starting Member

3 Posts

Posted - 2012-02-08 : 17:09:35
I can adjust it in Excel, so it's more of a nit-pic than anything. Being a newbie at SQL, I was trying to see if I wasn't doing it right or another reason. I am about to build a R2 server shorlty, so I might be able to resove the issue on it own. So far, these forums have helped me out as I am mainly a hardware guy.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-08 : 21:34:56
i dunno why its not working but I'd successful done this once using expression for formatting

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bokerjony
Starting Member

3 Posts

Posted - 2012-02-21 : 05:29:04
I have been a database programmer only for a lack spell of schedule, so I can be positive otherwise.

unspammed
Go to Top of Page

prestonm
Starting Member

1 Post

Posted - 2013-04-01 : 14:46:51
visakh16's example works when you add the .Value element to the date field after the IsNothing function.

as in...
"=IIF(IsNothing(Fields!PromisedByDate.Value),"", FormatDateTime(Fields!Date.Value, DateFormat.ShortDate))"

Regards,
Preston
Go to Top of Page
   

- Advertisement -