更改列数据类型和格式

本文介绍了更改列数据类型和格式的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

导入excel访问2007时，有些日期字段变成了文本字段.所以我想运行查询以将这些列数据类型更改为日期时间.如何在 sql 中传递日期格式(dd-mmm-yyyy")?

When importing the excel to access 2007, some date field had become text field. so i want to run query in access to change those columns data type to datetime. How can i pass the date format ("dd-mmm-yyyy") in sql ?

ALTER TABLE Tbl1 ALTER COLUMN [ABC] datetime

谢谢

乔

推荐答案

如果你从 Excel 中导入了一个列(字段)并且它最终变成了文本格式，你应该非常警惕简单地切换到日期时间格式.如果 Access 没有将该字段视为混合数据类型，则它应该已经将该字段解释为 DateTime.您应该创建一个新的 DateTime 列，并以明确的格式使用年月日更新它.

If you have imported a column (field) from Excel and it has ended up in text format, you should be very wary of simply switching to a date time format. Access should already have interpreted the field as DateTime if it did not see it as mixed data type. You should create a new DateTime column and update it with the year month and day in an unambiguous format.

假设您的列是 mm/dd/yyyy，一种选择是:

Let us say your column is mm/dd/yyyy, one option would be:

UPDATE Table SET NewDateTime = Right(DT,4) & "/" & Left(DT,2) & "/" & Mid(DT,4,2) WHERE DT Like "##/##/####"

您不能使用 DDL 设置格式属性，并且它在 MS Access 之外没有相关性.一般来说，我建议您不要在表格中使用 Format 属性，最好在表格和报表中进行控制.当查询没有以预期的方式工作时，设置格式不可避免地会在某个阶段混淆某些人.

You cannot set the Format property with DDL and it is of no relevance outside MS Access. In general, I recommend that you do not use the Format property in tables, it is best controlled in forms and reports. Setting a format will inevitably confuse someone at some stage when a query does not work in the expected way.

如果确实必须设置格式，则必须使用 VBA、DAO 和 TableDef.

If you really must set a format, you must use VBA, DAO and the TableDef.

Dim db As Database Dim tdf As TableDef Dim fld As DAO.Field Dim prp As Property Dim prpName As String Dim prpValue As String Dim prpType As Long Set db = CurrentDb Set tdf = db.TableDefs("Table1") Set fld = tdf.Fields("ADate") prpName = "Format" prpValue = "yyyy/mm/dd" prpType = dbText On Error Resume Next fld.Properties(prpName) = prpValue If Err.Number = 3270 Then Set prp = fld.CreateProperty(prpName, prpType, prpValue) Err.Clear fld.Properties.Append prp End If Debug.Print fld.Properties("Format")