Convert String to Date

[dc_sc]

The dates in my table are strings that appear as 12305 (1/23/05) and so forth. So some are 5 and some are 6 digits depending on the 1 or 2 digit month. I want to convert them in my query to a date field. I think I have to use the DateSerial and extract the parts of the date, but that leading 0 that is not there is throwing me off. Any easy solutions?

 

[Sergeant]

To pull the 1 or 2 digit month from the left:
Left(MyDate, Len(MyDate)-4)

To pull the 2 digit day from the middle:
Mid(MyDate, Len(MyDate)-3, 2)

And of course:
Right(MyDate,2)

 

[dc_sc]

Thank you. That worked great.

One more little thing. It formatted the date like this: 12/28/1944. That is fine, except some of the people born in the twenties got birthdates like this: 2/21/2028. Is there a way to get it to just do dates in the past or something like that? (I added 1900 to the year and that works, but there must be a correct way to do it.)