CODEWITHSUNDEEP
exceladodbvba
Dan
Dan

Reputation: 45741

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

Upvotes: 1

Views: 2612

Answers (1)

Our Man in Bananas
Our Man in Bananas

Reputation: 5981

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

So, check the following:

  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!

Upvotes: 3

Related Questions