MS-access wrong date format when converting field from text to date/time

Question

Hi,

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

I want to convert the field to a date/time but access formats it as dd/mm which makes it so that if the day is bigger then 12 or not the converted date might be wrong.

Example with the current format when stored as text in the DB:

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

If I simply 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:

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

How would I go about fixing the stored values? I don't care much about the format the dates will be showing in as I'll be able to easily change how it looks but getting them to convert properly from text to date/time has proven to be tricky.

Preferable I'd like to fix it from access directly but I can do it from C# if needed.

Thanks.

Answer 1

If this is a local Access application, it uses your system's date time format, so changing your localization settings in Windows to use MM/DD will make Access convert that way, unless this has been overridden somewhere in the app.

Answer 2

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

Answer 3

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

Answer 4

You've got good answers on your immediate problem however you appear to be running your system in dmy format. Thus you should be aware of the following.

SQL statements require that the dates be either completely unambiguous or in mm/dd/yy, or mm/dd/yyyy format. Otherwise Access/Jet will do it's best to interpret the date with unknown results depending on the specific date it is working with. You can't assume that the system you are working on is using those date formats. Thus you should use the logic at the following web page.

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