excel - 从 ADODB 查询返回日期(不是字符串)

Question

下面的函数工作正常并输出正确的结果，除了它将日期输出为字符串而不是日期。我怎样才能让它输出日期呢？

Function GetExpiries_YieldX(TradeDate As Date, Code As String) As Variant

    'Create and open the connection
    Dim oConnection As Connection
    Set oConnection = New Connection
    oConnection.ConnectionString = strConnectionStringYieldX
    oConnection.Open

    'Create the command object
    Dim oCommand As Command
    Set oCommand = New Command
    oCommand.CommandType = adCmdText

    Dim SQLString As String

        SQLString = "SELECT DISTINCT Expiry" _
                 & " FROM MTM" _
                 & " WHERE TradeDate = ?" _
                 & "   and Code = ?"

    oCommand.CommandText = SQLString
    oCommand.ActiveConnection = oConnection

    oCommand.Parameters.Append oCommand.CreateParameter("Date", adDBTimeStamp, adParamInput)
    oCommand.Parameters.Append oCommand.CreateParameter("Code", adVarChar, adParamInput, 50)

    oCommand.Parameters.Item("Date").Value = TradeDate
    oCommand.Parameters.Item("Code").Value = Code

    Dim result As New ADODB.Recordset
    Set result = oCommand.Execute

    Dim resultA As Variant
    'GetExpiries_YieldX = WorksheetFunction.Transpose(result.GetRows)
    GetExpiries_YieldX = result.GetRows

    oConnection.Close

End Function

Answer 1

2.8 之前的 ADODB 版本无法识别新的 日期 SQL Server 2008 中引入的数据类型。

因此，请检查以下内容:

确保您在连接字符串中使用 native SQL Server 提供程序 ( NOT OLEDB SQL 提供程序 )，因此它应该类似于 "Provider=SQLNCLI10.1;Data Source=MyServer;Initial Catalog=MyDatabase;Uid =我的用户；密码=我的密码；"

确保您已安装最新的 SQL Native Client(sqlncli2008.msi 或类似的东西，您可以从 SQL Server 2008 可再发行组件中窃取它)

确保您的 VB 项目中引用的 ADO 版本是 2.8

在 ADODB & SQL 2008 (MS Forums) 上找到所有内容

我希望这可以帮助您从数据库中的日期列中获取数组中的日期列!