Menu
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.
790
Access provides several predefined formats for date and time data. Open the table in Design View. In the upper section of the design grid, select the Date/Time field that you want to format. In the Field Properties section, click the arrow in the Format property box, and select a format from the drop-down list.
Any suggestions? first convert that yyyymmdd string to a proper yyyy-mm-dd string (e.g. add the dashes), then do an alter table to change the string field type to a date type. As long as the string LOOKS like a valid date, access will auto-convert the string to a real date value.
On an Access form, use the Date Picker to enter the current date. If the field is set up as a Date/Time field, the Date Picker icon appears when you click in the field. Click the icon, and then click the Today button below the calendar.
Access opens the table in Datasheet view. Select the field (the column) that you want to change. On the Fields tab, in the Properties group, click the arrow in the drop-down list next to Data Type, and then select a data type. Save your changes.
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.
138
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
