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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 SSRS - format date with null entries
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

spward
Starting Member

USA
3 Posts

Posted - 02/07/2012 :  16:45:06  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 02/07/2012 :  20:35:28  Show Profile  Reply with Quote
"=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

USA
3 Posts

Posted - 02/08/2012 :  09:21:58  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 02/08/2012 :  14:21:08  Show Profile  Reply with Quote
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

USA
3 Posts

Posted - 02/08/2012 :  17:09:35  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 02/08/2012 :  21:34:56  Show Profile  Reply with Quote
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

India
3 Posts

Posted - 02/21/2012 :  05:29:04  Show Profile  Reply with Quote
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 Posts

Posted - 04/01/2013 :  14:46:51  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000