| 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 MVPhttp://visakhm.blogspot.com/ |
 |
|
|
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 |
|
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
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. |
|
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
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 |
|
|
|
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 |
|
|
|
|
SSRS - format date with null entries