Report Builder 3.0: How to convert string representation of dates in mm/dd/yy format using CDate

Question

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!

Answer 1

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))