时间戳

首页 > 解决方案 > SQL Server 时间数据类型与 Excel 不兼容？

sql-server - SQL Server 时间数据类型与 Excel 不兼容？

问题描述

问题：

通过VBA方式从SQL Server（2014、2016）导入时间数据，CopyFromRecordset()不是时间而是Standard类型。

例子：

Excel VBA 中使用的 SQL 查询

SELECT TOP 100
    [CreationDate],                                     -- Works as expected Datetime; data type of this field is DATETIME 
    CAST(CreationDate AS DATETIME) AS CreationDate2,    -- Works as expected Datetime
    CAST(CreationDate AS TIME(0)) AS CreationTime       -- In Excel the type is Standard and not Time as it should be.
FROM 
    [StackOverflow2013].[dbo].[Comments]

VBA代码：

Sub TimeImport()
    Dim Conn As New ADODB.Connection
    Dim RS As New ADODB.Recordset
    Dim SQL As String

    Const ConnStr  As String = "Provider=SQLOLEDB; Data Source=PC\\MSSQLSERVER2014; Initial Catalog = StackOverflow2013; Integrated Security=SSPI;"

    'SQL see above
    Conn.Open ConnStr
    RS.Open SQL, Conn

    ActiveSheet.Cells(1, 1).CopyFromRecordset RS

    RS.Close
    Conn.Close

    Set RS = Nothing  
    Set Conn = Nothing
End Sub

导入后，如果我用 F2 在 Excel 的时间列中输入一个单元格并按 Enter，它会将数据类型正确更改为时间。但是，仅将单元格的格式更改为时间是行不通的。

如果我按记录导入数据记录并使用CDate()函数将时间字段转换为日期，那么它可以工作。但是，由于逐行处理的性能不佳，这对我来说不是一个选择。

以前有没有人遇到过这个问题，解决方案是什么？

感谢您的任何提示。

标签： sql-serverexcelvbatimetype-conversion

解决方案

我发现了以下可行的解决方法：

  1. 导入数据后，我将数据范围内的数据复制到同一范围内。

如果数据在A1:D100VBA 代码中，则如下所示：

ActiveSheet.Range("A1:D100").Value = ActiveSheet.Range("A1:D100").Value

  1. 我没有使用记录集对象的GetRowsCopyFromRecordset()函数将记录集数据保存到数组中，然后将数组放入电子表格中，如下所示：

    Sub TimeImport2()

    Dim Conn As New ADODB.Connection
    Dim RS As New ADODB.Recordset
    Dim SQL As String

    Dim myArr As Variant
    Dim RecCount As Long

    Const ConnStr  As String = "Provider=SQLOLEDB; Data Source=PC\\MSSQLSERVER2014; Initial Catalog = StackOverflow2013; Integrated Security=SSPI;"

    ' SQL see above

    Conn.Open ConnStr
    RS.Open SQL, Conn

    myArr = RS.GetRows
    myArr = Application.WorksheetFunction.Transpose(myArr)
    RecCount = UBound(myArr)

    ActiveSheet.Range("A1:D" & RecCount) = myArr

    RS.Close
    Conn.Close

    Set RS = Nothing  
    Set Conn = Nothing
End Sub

第二种方法的好处是您可以单独获取记录集的字段和行并像这样处理它们：

.Range("A2:A" & RecCount) = Application.Index(myArr, , 4)

为什么 Excel 无法识别 SQL Server 的 Time 数据类型或获取该CopyFromRecordset()方法损坏的数据类型仍然是个谜？

推荐阅读