SQL Server Forums
Profile | Register | 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
 New Topic  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
52317 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
52317 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
52317 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  
 New 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.05 seconds. Powered By: Snitz Forums 2000