String to date in MS Access SQL statement gives type mismatch error

Question

Currently doing a data migration from a Microsoft Access database to a Microsoft SQL Server database using C#. I am trying to create a query to pull data from the Access database and order by two columns: Surname and Date. The challenge is that Date is a string in the following sample format: 12.01.13 (i.e. YY.MM.DD), which is supposed to represent the 13th of January 2012. So I tried the following query in Access:

SELECT * FROM [Contacts 2012]
  order by Surname, CDate(Format(Date, "0000-00-00"));

However, I receive this error:

Data type mismatch in criteria expression

So I figure I am getting close. I tried a few different formats, plus maybe DateValue, but to be honest I can't remember. I have looked at other posts both in and outside of stackoverflow, but to no avail.

Answer 1

Use Regex.Replace for date format and Regex.Split to add 2 digits to year.

Answer 2

You said your dates are strings in YY.MM.DD format. If that is correct for all the stored [Date] values ... which means Len([Date]) = 8 for all rows ... those values will sort in the same order whether you sort them as text or transform them to Date/Time values.

So I'll suggest this query ...

SELECT *
FROM [Contacts 2012]
ORDER BY Surname, [Date];

If that returns what you want, you can avoid the data type error you're getting when attempting to transform the strings to actual dates. Also, with an index on the [Date] column, the query will execute significantly faster.

Note this approach is not suitable if some of your [Date] values are not in YY.MM.DD format, eg "12.1.13".

Answer 3

Unsure how i actually finally resolved this but if memory serves i actually sorted the database manually by opening the database in access naturally and sorted by the surname column alphabetically and then sorted by the date column either manually or through a select statement.