Reporting Services 2005 - Conditional Date Formatting not working

...so my sql view returns nvarchar as '123456' or 'JAN 11 2008 12:00AM' (view does a union on two tables and casts date to nvarchar).

I'm trying to apply some conditiona开发者_Go百科l date formatting i.e. I want to format the date by checking the "Type" column as:

=IIf(Fields!Type.Value = "COS", Fields!CosNoOrDateToContractor.Value, FormatDateTime  (Fields!CosNoOrDateToContractor.Value, vbShortDate))

So basically if the type is a "COS" then just return the data as is, otherwise format it to a date.

When previewing the report if its a COS then I get #Error in the textbox (Conversion from string "123456" to type 'Date' is not valid.

It's anything else then data is formatted as date.

Does this make sense?

Anything wrong with the syntax or is what I'm trying to do not possible?

My guess is that you have some formatting in the text box "Format" field

Otherwise, you have a number when you expect a date.

Try adding an IsDate() check too to remove non-date

=IIf(Fields!Type.Value <> "COS" AND IsDate(Fields!CosNoOrDateToContractor.Value)
     , FormatDateTime (Fields!CosNoOrDateToContractor.Value, vbShortDate)
      , Fields!CosNoOrDateToContractor.Value)

Edit:

In the DataSet, what datatype does it say please?

Have modified the view instead so I'm not casting dates to nvarchar.