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!

Solution

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

Comments

[confusedKid]

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]

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

[Hari]

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

[confusedKid]

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.

[Hari]

YEs, you're right, sorry for that.I am editing the answer again, as you suggested. Sorry and thanks for pointing that!