需要将具有文本格式的字段转换为短日期格式 [英] Field with Text format needs to be converted to Short Date Format
问题描述
我有一个名为marriages的数据库。
marriages Table包含以下字段：
字段名称=结婚日期
数据类型=文字
---------------------- ----------------------------
该表包含9000多条记录。在婚姻日期栏目中记录如下：
1847/11/28
1917/02/04
1920/08/02
2011/5/4
11/12/1989
08/22/2009
8/22/2010
------ -------------------------------------
我需要将所有现有记录更改为以下格式，例如（短日期= 2007年6月19日）。
我将需要所有未来的婚姻登记日期在（短日期）格式。
实现这一目标的最佳方法是什么。任何帮助将不胜感激。在此先感谢。
I have a database with one Table called marriages.
marriages Table Contains the following Field:
Field Name = Date of Marriage
Data Type = Text
--------------------------------------------------
The table contains over 9000 records. With records such as the following in the Date of Marriage Column:
1847/11/28
1917/02/04
1920/08/02
2011/5/4
11/12/1989
08/22/2009
8/22/2010
-------------------------------------------
I need to change all the existing records to the following format for example (Short Date = 6/19/2007).
And I will need all future Date of Marriage Entries to be in the (Short Date) format.
What would be the best way to accomplish this. Any help would be greatly appreciated. Thanks in advance.
推荐答案
- 执行以下SQL / Query转换所有值[结婚日期]字段到短日期：
- Execute the following SQL/Query to Convert all Values in the [Date of Marriage] Field to Short Date:
目前您所拥有的是一个存储日期的文本字段婚姻，但至少有两种不同的表现形式（yyyy / mm / dd和dd / mm / yyyy）。
你需要的是一个真正的日期字段 - 它的格式不重要，因为这只是日期值表示给用户的方式，而没有任何内容关于它的存储方式。
你能确认现有的内容总是是d / m / yyyy形式，还是yyyy / m / D？如果是这样，可以自动将文本条目转换为真实日期。但是，如果某些日期以m / d / yyyy或yyyy / d / m的形式记录，则无法自动转换所有日期，因为在许多情况下，无法区分可能有效的日期（例如文本条目1950/5/4是代表1950年5月4日还是1950年4月5日？）
您需要在表中添加一个新字段来存储修订日期。不要尝试将现有字段转换为日期/时间表单，因为Access无法成功转换现有文本值。
请告知是否记录为文本的当前日期始终以两种形式之一记录：日/月/年和年/月/日。
此外，请告知是否使用除您所显示的斜杠字符之外的任何其他形式的分隔符（例如'。''），以及年份是否始终为四-digit form（自动化解决方案的另一个非常重要的部分）。
-Stewart
What you have at present is a text field which stores dates of marriage, but in at least two different representations (yyyy/mm/dd and dd/mm/yyyy).
What you need is a true date field - it''s format is not what counts, as this is simply the way the date value is represented to the user, and nothing at all to do with how it is stored.
Could you confirm that the existing contents will always be in either d/m/yyyy form, or yyyy/m/d? If so it is possible to automate the conversion of your text entries to true dates. If, however, some of the dates are recorded in the form m/d/yyyy or yyyy/d/m it will not be possible to convert all of them automatically, as in many cases it would be impossible to distinguish between potentially valid dates (e.g. does the text entry 1950/5/4 represent 4 May 1950 or 5 April 1950?)
You will need to add a new field to your table to store the revised date. Don''t attempt to convert the existing field to date/time form as it will not be possible for Access to convert the existing text values successfully.
Please advise on whether or not the current date recorded as text is consistently recorded in one of two forms: day/month/year and year/month/day.
Also, please advise whether any other form of separator character is used other than the slash character you show (e.g. ''.''), and whether or not years are always in four-digit form (another very important part of the potential for automating the solution).
-Stewart
@Stewart：
测试数据，如＃1中的OP指示成功转换（更新）。条目能够被强制转换为日期格式，然后正确格式化。
@Stewart:
The Test Data, as indicated by the OP in Post #1 converted (Updated) successfully. The entries were able to be coerced into a Date Format, then properly Formatted.
这篇关于需要将具有文本格式的字段转换为短日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
