Return dates (not strings) from an ADODB query

Question

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

is the column Expiry in the database defined as DataTime? — Our Man in Bananas
– Our Man in Bananas, Commented Apr 10, 2013 at 13:47
what are you doing with the resultset? If putting into a worksheet range, you could just dump the recordset into a range using Range(..).CopyFromRecordset method, then modify the format of those range using NumberFormat — Our Man in Bananas
– Our Man in Bananas, Commented Apr 10, 2013 at 13:49
@Philip no it's just date type (SQL Server 2008 btw), I am putting the dates on the worksheet. At the moment I'm just converting them to dates doing this sort of thing: =DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)) but I would prefer if they just came out as dates if possible. I find it very difficult to find decent documentation on how to write these sort of functions (i.e. vba functions that query sql server dbs) properly :/ — Dan
– Dan, Commented Apr 10, 2013 at 13:59
or you could try casting the Expiry field into a date format in your SQL string: "SELECT DISTINCT CDATE(Expiry)... — Excel Developers
– Excel Developers, Commented Apr 10, 2013 at 14:00

Our Man in Bananas · Accepted Answer · 2013-04-10 16:04:14Z

3

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

So, check the following:

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;"
Make sure you've installed latest SQL Native Client (sqlncli2008.msi or something like that, you can steal it from SQL Server 2008 redistributable)
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!

answered Apr 10, 2013 at 16:04

community wiki

Our Man in Bananas

Sign up to request clarification or add additional context in comments.

2 Comments

Dan Over a year ago

Just changing the connection string (i.e. step 1 n your answer) fixed it! Thanks!

Our Man in Bananas Over a year ago

you are most welcome, it was fun going back and forth to work out what was going on :)

Collectives™ on Stack Overflow

Return dates (not strings) from an ADODB query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related