views:

3860

answers:

4

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.

+1  A: 

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.

kscott
So simple... but it works! Thanks kscott.
tb
Hopefully this is a one time operation the OP is doing. Other wise, you are just about guaranteed that some user is going to change his/her date settings, and break the application.
CodeSlave
The usual methods for this problem are: use a non-ambiguous date format, such as dd-mmm-yyyy (e.g., 17-Jun-2009) or use DateSerial. Keep in mind that Jet SQL interprets non-specified dates in US form, i.e., mm/dd/yyyy. This is, of course, disastrous if your local date settings are dd/mm/yyyy.
David-W-Fenton
A: 

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.

Erika
I actually tried that at the beginning but it hasn't worked for me.
tb
+1  A: 

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

Jeff O
+1  A: 

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

Tony Toews
yyyy-mm-dd is unambiguous for the Access database engine and has the huge advantage of being ISO 8601 standard.
onedaywhen