How can I convert a text date format like mmm-yy into a date in a query in MS Access?

date, ms-access

Solution

"I need to extract month from the first 3 chars, year from last 2 digits, and combine them. Ideally I'd like to do this all within MS Access SQL."

Here is a session in the Immediate window.

BIRTHDT = "May-31"
? BIRTHDT
May-31
? Left(BIRTHDT,3) & "-1-" & Right(BIRTHDT,2)
May-1-31
? CDate(Left(BIRTHDT,3) & "-1-" & Right(BIRTHDT,2))
5/1/1931

So you could use that expression in a query.

SELECT CDate(Left(BIRTHDT,3) & "-1-" & Right(BIRTHDT,2))
FROM YourTable;

Problem

I have a text file with format mmm-yy (all months are 3 letter abbrev, e.g. Jan, Feb, Mar) ``` May-31 ``` Which means, "May, 1931". If I use the following in a query: ``` CDate([BIRTHDT]) ``` I get May 31, 2012, instead of May 1, 1931. The other rows, which have years that are later, like May-32, give the desired result of May 1, 1932. Obviously this has to do with the ms-access text to date conversion function mmm-dd validity checking having higher priority over the likely less common mmm-yy format, but it gives unexpected results in this case. So somehow I need to extract month from the first 3 chars, year from last 2 digits, and combine them. Ideally I'd like to do this all within MS Access SQL.

Original source