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)
我有一个访问数据库,其中所有日期都以mm / dd格式存储在文本字段中(例如:2009年3月13日上午12:20:36)
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.
我想将字段转换为日期/时间,但访问将其格式化为dd / mm,这使得如果日期大于12或者转换日期可能是错误的。
Example with the current format when stored as text in the DB:
在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.
我希望直接从访问中修复它,但如果需要,我可以从C#中进行修复。
Thanks.
4 个解决方案
#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.
如果这是本地Access应用程序,它将使用您系统的日期时间格式,因此在Windows中更改您的本地化设置以使用MM / DD将使Access进行转换,除非已在应用程序中的某处覆盖。
#2
Format(CDate("3/13/2009 11:32:40 PM"), "mm/dd/yyyy" will give you 03/13/2009
格式(CDate(“3/13/2009 11:32:40 PM”),“mm / dd / yyyy”将为您提供03/13/2009
#3
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.
对于您当前的问题,您已经得到了很好的答案,但是您似乎以dmy格式运行系统。因此,您应该了解以下内容。
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.
SQL语句要求日期完全明确或以mm / dd / yy或mm / dd / yyyy格式表示。否则,Access / Jet将最好根据其使用的具体日期来解释具有未知结果的日期。您不能假设您正在使用的系统正在使用这些日期格式。因此,您应该使用以下网页上的逻辑。
Return Dates in US #mm/dd/yyyy# format http://www.mvps.org/access/datetime/date0005.htm
美国退货日期#mm / dd / yyyy#format http://www.mvps.org/access/datetime/date0005.htm
#4
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.
Access中的Import函数具有不错的日期解析功能,可以让您指定相当多的不同格式。不知道如何最好地将其应用于Access中已有的东西 - 快速的方法可能是将数据复制到Excel,然后重新导入它。
#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.
如果这是本地Access应用程序,它将使用您系统的日期时间格式,因此在Windows中更改您的本地化设置以使用MM / DD将使Access进行转换,除非已在应用程序中的某处覆盖。
#2
Format(CDate("3/13/2009 11:32:40 PM"), "mm/dd/yyyy" will give you 03/13/2009
格式(CDate(“3/13/2009 11:32:40 PM”),“mm / dd / yyyy”将为您提供03/13/2009
#3
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.
对于您当前的问题,您已经得到了很好的答案,但是您似乎以dmy格式运行系统。因此,您应该了解以下内容。
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.
SQL语句要求日期完全明确或以mm / dd / yy或mm / dd / yyyy格式表示。否则,Access / Jet将最好根据其使用的具体日期来解释具有未知结果的日期。您不能假设您正在使用的系统正在使用这些日期格式。因此,您应该使用以下网页上的逻辑。
Return Dates in US #mm/dd/yyyy# format http://www.mvps.org/access/datetime/date0005.htm
美国退货日期#mm / dd / yyyy#format http://www.mvps.org/access/datetime/date0005.htm
#4
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.
Access中的Import函数具有不错的日期解析功能,可以让您指定相当多的不同格式。不知道如何最好地将其应用于Access中已有的东西 - 快速的方法可能是将数据复制到Excel,然后重新导入它。