SSRS Converting int to date and handling #Error

Question

I'm a bit of an SSRS newbie and I'm having some trouble converting an Int to a Date in SSRS. The publication_date.Value is fed from a proprietary database as an Int type (so we unfortunately have no control over that) in the format 20160519.

The following works splendidly when there is a value present:

=CDate(Left(Fields!publication_date.Value, 4) + "-" + Mid(Fields!publication_date.Value, 5, 2) + "-" + Right(Fields!publication_date.Value, 2))

However, when there is a null it returns an #Error, which I'd like to suppress. I've tried using an IIf statement but without success.

=Iif(Fields!publication_date.Value is nothing,"", CDate(Left(Fields!publication_date.Value, 4) + "-" + Mid(Fields!publication_date.Value, 5, 2) + "-" + Right(Fields!publication_date.Value, 2)))

I've been digging around for other solutions but yet to find anything that works. Please help!

Edited - thanks for the duplicate article suggestion, however I need to convert an Int to a Date and handle the errors, not a Date to an Int.

Could you try this [answer](http://stackoverflow.com/a/27409605/2451726) — Arulkumar, May 19 '16 at 10:00
Possible duplicate of [Convert a date to an integer in YYYYMMDD format in SSRS](http://stackoverflow.com/questions/31253180/convert-a-date-to-an-integer-in-yyyymmdd-format-in-ssrs) — BIDeveloper, May 19 '16 at 10:34

score 0 · Accepted Answer · answered May 19 '16 at 11:13

I had the same issue,

As per your example, do one thing.

Keep you expression same like below,

=CDate(Left(Fields!publication_date.Value, 4) + "-" + Mid(Fields!publication_date.Value, 5, 2) + "-" + Right(Fields!publication_date.Value, 2))

Now, tricky part would be, in textbox visibility use below expression.

=IIf(IsNothing(Fields!publication_date.Value),True,False)

It will definitely work. Let me know if you face any issue. Thanks.

SSRS Converting int to date and handling #Error

1 Answers1