Convert DDMMYYYY varchar value to MM-dd-yyyy in SSRS

Question

I wanted to convert DDMMYYYY formated data (data type is varchar) to MM-dd-yyyy format using SSRS expression only.

I tried the following but it is showing #Error

=IIF(NOT(IsNothing(Fields!DoB.Value)),CDate(Fields!DoB.Value).ToString("MM-dd-yyyy"),Nothing)

=IIF(NOT(IsNothing(Fields!DoB.Value)),Format(CDate(Fields!DoB.Value),"MM-dd-yyyy"),Nothing)

It I try like below then it shows MM-dd-yyyy,

=Format(Fields!DoB.Value, "MM-dd-yyyy")

Answer 1

I don't understand why you pass it as a string in your report. Anyway here it is.

I tested it using this expression:

= Mid("25052016",3,2)  + "-" + Left("25052016",2) + "-" + Right("25052016", 4)

and this is my output:

05-25-2016

• Basically what I did here is just separate your string in three chunks separated with - to achieve your goal.

So in your example expression you could try it like this:

=IIF(NOT(IsNothing(Fields!DoB.Value)),(Mid(Fields!DoB.Value,3,2)  + "-" + Left(Fields!DoB.Value,2) + "-" + Right(Fields!DoB.Value, 4)),Nothing)

Note: I'm not 100% sure that the expression just above this comment will work in your end because I haven't tested it but atleast I showed to you my concept on my other example.