从ADODB查询返回日期（而不是字符串） [英] Return dates (not strings) from an ADODB query

问题描述

下面的功能可以正常运行，并输出正确的结果，除了输出日期作为字符串，而不是日期。如何获取输出日期？

The function below works fine and outputs the correct result except it outputs the dates as strings and not as dates. How can I get it to output dates instead?

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

推荐答案

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

ADODB versions prior to 2.8 had aproblem recognizing the new Date datatype introduced in SQL Server 2008.

所以，检查以下内容：

1. 确保您正在使用连接字符串中的本机 SQL Server提供程序（ NOT OLEDB SQL提供者），因此它应该是类似于Provider = SQLNCLI10.1;数据源= MyServer;初始目录= MyDatabase; Uid = MyUser; Pwd = MyPassword;


2. 确保您已安装最新的SQL Native Client（sqlncli2008.msi或类似的内容您可以从SQL Server 2008再分发中窃取）


3. 确保VB项目中引用的ADO版本为2.8

1. Ensure you are using the native SQL Server Provider (NOT OLEDB SQL provider) in your Connnection string, so it should be something like "Provider=SQLNCLI10.1;Data Source=MyServer;Initial Catalog=MyDatabase;Uid=MyUser;Pwd=MyPassword;"
2. Make sure you've installed latest SQL Native Client (sqlncli2008.msi or something like that, you can steal it from SQL Server 2008 redistributable)
3. Ensure ADO version referenced in your VB Project is 2.8

发现所有在

found all that on ADODB & SQL 2008 (MS Forums)

希望能帮助您从数据库的日期列中获取数组中的日期列！

I hope that helps you get a Date column in your array from a Date column in your db!

这篇关于从ADODB查询返回日期（而不是字符串）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！