6

I'm building a report using Report Builder 3.0.

The source system I'm working with has strings representing dates in the format of mm/dd/yy, but I want to show this to the end users as dd-MMM-yy instead. Using CDate on the string will give me errors because it's interpreting the string in the dd/mm/yy format (US regional settings).

Is there a way to convert the string to a date correctly without changing the regional settings (this isn't really an option)?

Thanks!

confusedKid
  • 3,231
  • 6
  • 30
  • 49

1 Answers1

9

You can use Format command and specify the exact format you require. For example:

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

or you can try this:

=Day(Fields!Date.Value) & "/" & Month(Fields!Date.Value) & "/" & Year(Fields!Date.Value)

EDIT: This is OK:

=Cdate(Mid(Fields!Date.Value,4,2) & "/" & Mid(Fields!Date.Value,1,2) & "/" & Mid(Fields!Date.Value,7,4))
Hari
  • 1,509
  • 15
  • 34
  • CDate will return an error if the Date field isn't in the "dd/mm/yyyy" format already. For example, if I have "01/15/2012", CDate will think that the month field is "15" and throw an error. – confusedKid Mar 28 '12 at 20:48
  • This gives the same result, because `Day`, `Month`, and `Year` are expecting the respective values to be "dd-mm-yyyy" format. `Month` will always check the middle part of the string for the month value... – confusedKid Mar 30 '12 at 19:20
  • Got it.So, the field you want to convert is a string? Why not manipulate it as a string to get what you want? Let's say you have a date "02/23/2011", I suggest you this: `Cdate(Mid(Fields!Date.Value,4,5) & "/" & Mid(Fields!Date.Value,1,2) & "/" & Mid(Fields!Date.Value,7,10))` – Hari Mar 30 '12 at 20:51
  • Just wanted to make an amendment: Mid(Fields!Date.Value,4,5) should actually be Mid(Fields!Date.Value,4,2) (same with the other Mid calls). The 3rd parameter is the number of characters to grab, not the ending position of the string. – confusedKid Apr 20 '12 at 18:44
  • YEs, you're right, sorry for that.I am editing the answer again, as you suggested. Sorry and thanks for pointing that! – Hari Apr 20 '12 at 20:12