Converting to Date Format MM/DD/YYYY from MMDDYYYY

[ghost2]

Hello all. I am getting the month and day from a table in a SQL database where I have to strip out any "/" or dashes because of an issue with user data entry. For example, 12/31 might be
1231 or 12-31 or 12/31. Another issue is lets say March 1st might be,
0301 or 3-01 or 03/01 etc.

So after I get that I attach the current year so lets say I might have 3012003 in a string. How can I convert this to be in date format the best way as MM/DD/YYYY. Thanks all.

 

[benlinkknilneb]

Hi,

I had a similar problem several weeks ago. I wrote this function to do the conversion. It accepts mmddyyyy and returns mm/dd/yyyy.

Public Function ReformatAsDate(str2 As String) As String
Dim x As Integer
Dim y As String
Dim y2 As String
Dim y3 As String
y = Mid(str2, 5, 2)
y2 = Right(str2, 2)
y3 = Left(str2, 4)
ReformatAsDate = y & "/" & y2 & "/" & y3
End Function

Hope it helps!
Ben

 

[benlinkknilneb]

Whoops! That's in VBA; the Left() and Right() Functions probably won't work... you'll have to change those lines to read:

y2 = Mid(str2,str2.Length()-1)
y3 = Mid(str2,1,4)

Also, you obviously won't need to declare x, either... my function did something else at the same time and I didn't get the other junk cleaned out completely... sorry 'bout that. Your final function should read as follows:

Public Function ReformatAsDate(ByVal str2 As String) As String
Dim y As String = Mid(str2, 5, 2)
Dim y2 As String = Mid(str2,str2.Length()-1)
Dim y3 As String = Mid(str2,1,4)
ReformatAsDate = y & "/" & y2 & "/" & y3
End Function

 

[ghost2]

Thanks benlinkknilneb

 

[ghost2]

One more question though. How do I deal with if the user puts in the month day as 331 instead of 0331

 

[benlinkknilneb]

You could check the length of str2 when it comes in, and attach 0's in the appropriate locations. That should be a simple check (if it's 8 characters long, you're ok, if it's 7 add a 0 in front of month OR day, if it's 6 add a 0 in front of month AND day). Just do the test before the first "Dim" above.

 

[RiverGuy]

Using masked edit boxes is a good way to ensure the format is good.

 

[markinro]

What about the datetimepicker control ?

Regular expressions ?

 

[RiverGuy]

Yeah, datetimepicker would be good too.