Formatting any date into MM/dd/yyyy

Question

Hi I'm currently working on some SSRS reports and the data I am getting comes in quite a range. I'm getting dates in forms of: 6/18/2010 6:00:00 AM, 6/18/2010, 2010/6/18

I was hoping to be able to come up with a formatting code to use for the row so that it would convert any of these into the MM/dd/yyyy format and anything else just leave untouched. Unfortunately the scope of my VB skills do not compliment my objective. I was hoping someone might be able to help me with this. I was thinking something along the lines of iif(me.Value=####/##/####@##:##:##@@&, MM/dd/yyyy, nothing) but that doesn't seem to work.

Thanks in advance.

EDIT: =iif(isDate(me.Value), "MM/dd/yy", nothing) works for everything except something like 2010/06/18.

Answer 1

Have you tried String.Format("{0}", my_formatting)

http://idunno.org/archive/2004/14/01/122.aspx

Answer 2

Maybe you can take a look at the Date.Parse method. Some examples are available here

Answer 3

\b(?<year>\d{4})/(?<month>\d{1,2})/(?<day>\d{1,2})\b|\b(?<month>\d{1,2})/(?<day>\d{1,2})/(?<year>\d{4})\b

will extract year, month and day from your string. It expects a four-digit year, accepts only slashes as separators, and expects months always before days.

It doesn't do any validation (checking for valid dates), but this is not something regexes are very good at - this should be done programmatically if it's at all necessary.

So in Visual Basic, this should look like

Dim RegexObj As New Regex("\b(?<year>\d{4})/(?<month>\d{1,2})/(?<day>\d{1,2})\b|\b(?<month>\d{1,2})/(?<day>\d{1,2})/(?<year>\d{4})\b")
year = RegexObj.Match(SubjectString).Groups("year").Value
month = RegexObj.Match(SubjectString).Groups("month").Value
day = RegexObj.Match(SubjectString).Groups("day").Value

Answer 4

I can't seem to comment. Is this VB classic (VB6) or is it VB.NET?