0

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")
Pedram
  • 6,256
  • 10
  • 65
  • 87
  • It would appear that you have already answered this question yourself.... http://stackoverflow.com/questions/23842365/display-date-in-mm-dd-yyyy-format-ssrs-report – BIDeveloper May 19 '16 at 09:06
  • @BIDeveloper - yes I have given answer. but here requirement is different. can you please read my question properly. DDMMYYYY having varchar type. – Pedram May 19 '16 at 09:19

2 Answers2

0

Have you tried to parse the field value, like this? 

=Format(CDate(Mid(Fields!DoB.Value,3,2) & "-" & Left(Fields!DoB.Value,2) & "-" & Right(Fields!DoB.Value,4)), "MM-dd-yyyy")
R. Richards
  • 24,603
  • 10
  • 64
  • 64
0

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.

bot
  • 4,841
  • 3
  • 42
  • 67