The format of the wrong MS-access date when converting a field from text to date / time

Question

I have an access database provided to me where all dates are stored in a text field in mm / dd format (for example: 3/13/2009 12:20:36 AM)

I want to convert the field to a date / time, but the format for accessing it is dd / mm, which makes it so that if the day is more than 12 or not, then the converted date may be wrong.

An example with the current format when saving to text in DB:

  3/12/2009 11:32:40 PM
 3/13/2009 11:32:40 PM

If I just convert the data type of this field from the design view of the table from text to date / time Date Type I get the following:

  12/03/2009 11:32:40 PM
 03/13/2009 11:32:40 PM

How can I fix the stored values? I do not care about the format in which the dates will be displayed, since I will be able to easily change how it looks, but it turned out to be difficult to get them to correctly convert text to date / time.

Preferably I would like to fix this from direct access, but I can do it with C # if necessary.

Thanks.

Answer 1

If this is a local access application, it uses the format of your system date, so changing the localization settings in Windows to use MM / DD will do the Access conversion in this way if it has not been redefined somewhere in the application.

Answer 2

The format (CDate ("3/13/2009 11:32:40 PM"), "mm / dd / yyyy" will give you 03/13/2009

Answer 3

You have good answers to your immediate problem, but you seem to be using your system in dmy format. Therefore, you should know the following.

SQL statements require that dates be either completely unique, or in the format mm / dd / yy or mm / dd / yyyy. Otherwise, Access / Jet will do its best to interpret the date with unknown results depending on the specific date with which it works. You cannot assume that the system you are working on uses these date formats. Therefore, you should use the logic on the next web page.

Return dates in the format US # mm / dd / yyyy # http://www.mvps.org/access/datetime/date0005.htm

Answer 4

The import function in Access has great date parsing functionality and allows you to specify quite a few different formats. Not sure how best to apply this to something already in Access - a quick way might be to copy the data into Excel and then re-import it.