更改列数据类型和格式 [英] Change column data type and format

问题描述

导入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")

这篇关于更改列数据类型和格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！