vba 将 SQL Server 日期数据类型导入 Excel 日期格式的问题

Issue with importing SQL Server date datatypes into Excel date formats

提问by John Smizz

I am importing data into Excel from a SQL Server 2012 database. The issue I have is that SQL Server 2012 Datedatatype columns are not being recognised consistently in Excel.

我正在将数据从 SQL Server 2012 数据库导入 Excel。我遇到的问题是 SQL Server 2012Date数据类型列在 Excel 中无法一致识别。

If I use ADO Recordsets to import data from specific columns, data with column datatype Dateor Datetimeis copied into the Recordset as VBA datatype String(instead of Dateor Integer).

如果我使用 ADO Recordsets 从特定列导入数据，则具有列数据类型的数据Date或Datetime作为 VBA 数据类型String（而不是Date或Integer）复制到记录集中。

Reproducing the Recordset in Excel yields strings in the format yyyy-mm-dd. These cells are not recognized by Excel as date/time, even if I change the formatting.

在 Excel 中再现 Recordset 会产生格式为 的字符串yyyy-mm-dd。即使我更改了格式，Excel 也不会将这些单元格识别为日期/时间。

Yet when I refer to the Excel cell from another cell the Datetype is recognized (eg: A1 contains the result of my SQL VBA Recordset query, for example "2013-04-17". I enter into cell A2 "=A1 + 1", A2 will display it as 41382.

然而，当我从另一个单元格引用 Excel 单元格时，Date类型被识别（例如：A1 包含我的 SQL VBA Recordset 查询的结果，例如“2013-04-17”。我输入单元格 A2“=A1 + 1” , A2 将显示为 41382。

However, when I use MS Query to extract data from the same Database using ListObjects and ODBC, the same data from the same database is returned with dates being interpreted correctly by Excel (ie I import a column called "Transaction Date" with SQL datatype Datetimevia MS Query the result will be a MS Excel Date "Integer").

但是，当我使用 MS Query 从使用 ListObjects 和 ODBC 的同一个数据库中提取数据时，返回同一个数据库中的相同数据，并且 Excel 正确解释了日期（即，我Datetime通过SQL 数据类型导入了一个名为“事务日期”的列）MS Query 结果将是 MS Excel 日期“整数”）。

How do I amend my VBA Recordset code to treat data in the format yyyy-mm-ddas dates instead of strings?

如何修改我的 VBA Recordset 代码以将格式的数据yyyy-mm-dd视为日期而不是字符串？

Thanks, John

谢谢，约翰

PS: SQL queries I execute vary so that sometimes I get data from columns with datatype "date" and sometimes its just integers or Double datatype. This means that hardcording "Convert(INT, TransactionDate)" into the SELECT statement is not really possible. However, I am a total amateur at SQL so there might be a super easy solution to this (eg if I ever access columns with datatype "DateTime" then SQL should always send "CONVERT(DBL, XXX)" instead of XXX where XXX is a column with datatype DATE.

PS：我执行的 SQL 查询各不相同，因此有时我从数据类型为“date”的列中获取数据，有时只是整数或 Double 数据类型。这意味着将“Convert(INT, TransactionDate)”硬编码到 SELECT 语句中是不可能的。但是，我完全是 SQL 的业余爱好者，因此可能有一个超级简单的解决方案（例如，如果我曾经访问过数据类型为“DateTime”的列，那么 SQL 应该始终发送“CONVERT(DBL, XXX)”而不是 XXX，其中 XXX 是数据类型为 DATE 的列。

Function GetSQL(strQuery As String) As Variant

Dim rst As ADODB.Recordset
Dim element As Variant
Dim i, j As Integer
Dim v As Variant
On Error GoTo aError


Call ConnecttoDB

cnt.Open
Set rst = New ADODB.Recordset
rst.Open strQuery, cnt, adOpenStatic
rst.MoveFirst
If rst.RecordCount = 0 Then   'i.e. if it's empty
    v = CVErr(xlErrNA)
    rst.Close
    cnt.Close
Else
End If
 v = rst.GetRows

For i = 0 To UBound(v, 1)
    For j = 0 To UBound(v, 2)
        If v(i, j) = -9999 Then
            v(i, j) = CVErr(xlErrNA)
        Else
        End If
    Next j
Next i
GetSQL = Application.WorksheetFunction.Transpose(v)
rst.Close
cnt.Close
Exit Function

aError:
MsgBox Err.Description
rst.Close
cnt.Close
End Function

采纳答案by John Smizz

The issue arose from using the Excel function Transpose instead of looping through the contents of the Recordset results object.

该问题源于使用 Excel 函数 Transpose 而不是循环遍历 Recordset 结果对象的内容。

In the process of Transposing via Excel some information included in the Recordset (such as for eample Datatype) seems to get lost. This results in dates being interpreted as strings by Excel.

在通过 Excel 转置的过程中，Recordset 中包含的一些信息（例如示例数据类型）似乎丢失了。这会导致日期被 Excel 解释为字符串。

Instead of using the Excel Transpose function I guess youre meant to use a loop, as that avoids the loss of information described above.

而不是使用 Excel Transpose 函数，我猜你的意思是使用循环，因为这样可以避免上述信息的丢失。

回答by dee

The type of field in ado recodset can't be changed but have you tried UDT? Here a short sample with user defined type which will wrap the ado recordset. Then in your code you will use your own recordset which will use Date data type.

ado recodset 中的字段类型无法更改，但您是否尝试过 UDT？这是一个带有用户定义类型的简短示例，它将包装 ado 记录集。然后在您的代码中，您将使用自己的记录集，该记录集将使用 Date 数据类型。

Add reference to Microsoft ActiveX Data Objects Library.

添加对 Microsoft ActiveX 数据对象库的引用。

Standard module vba code:

标准模块 vba 代码：

Private Const CONNECTION_STRING As String = "Provider=sqloledb;Data Source=SQLSERVER2012;Initial Catalog=Test;Integrated Security=SSPI;"
Private Const SQL_QUERY As String = "select * from dbo.DateTest"

Public Type MyRecord
    Id As Long
    CreatedAt As Date
End Type

Public Sub test()
    Dim myRecodset() As MyRecord
    myRecodset = GetSQL
End Sub

Public Function GetSQL() As MyRecord()

    On Error GoTo aError

    Dim adodbConnection As ADODB.Connection
    Set adodbConnection = New ADODB.Connection
    With adodbConnection
        .ConnectionString = CONNECTION_STRING
        .CursorLocation = adUseServer
        .Open
    End With

    Dim newRecordset() As MyRecord
    Dim newRecord As MyRecord
    Dim rowIndex As Long
    Dim adodbRecordset As ADODB.Recordset

    Set adodbRecordset = New ADODB.Recordset
    With adodbRecordset
        .Open SQL_QUERY, adodbConnection, adOpenStatic
        .MoveFirst

        ReDim newRecordset(.RecordCount - 1)

        Do While Not .EOF
            newRecord.Id = .Fields("Id")
            newRecord.CreatedAt = .Fields("CreatedAt")
            newRecordset(rowIndex) = newRecord
            rowIndex = rowIndex + 1
            .MoveNext
        Loop

    End With

    GetSQL = newRecordset

aError:
    If (Err.Number <> 0) Then MsgBox Err.Description
    adodbRecordset.Close
    adodbConnection.Close
End Function

Tested with this table:

用这个表测试：

CREATE TABLE [dbo].[DateTest]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CreatedAt] [date] NOT NULL
)