What is the equivalent datatype for datetime of SQL Server 2008 in ADO?

The datetime datatype in SQL Server 2008 supports milliseconds.

I am trying to execute a stored procedure that accepts a datetime parameter, with an arg with milliseconds, as input/output value.

I am unable to convert the string that I pass in to the method as datetime value. When I do not pass in the millisecond values, the conversion happens correctly.

I see the conversion problem in the below method.

Set objpara2 = objCom.CreateParameter("datetime", adDate, adParamInputOutput, , "2011-01-01 11:01:01.123")

What is the equivalent datatype for datetime of SQL Server 2008 in ADO?

Here is the complete code snippet:

CREATE PROCEDURE [dbo].[TestProc] @time1 time, @datetime datetime output
as
begin
    SET NOCOUNT ON;
    select @datetime = datetime from ALLTimeTypes where time = @time1;
end

Private Sub Command1_Click()
    Dim objCon As ADODB.connection
    Dim objCom As ADODB.command
    Dim objPara As ADODB.Parameter
    Dim objpara2 As ADODB.Parameter
    Dim objRS As ADODB.Recordset
    Dim k As Integer

    Set objCon = New ADODB.connection
    Set objCom = New ADODB.command

    objConConnectionString = "Provider=SQLNCLI10;" _
      开发者_如何学编程   & "Data Source=ES-89W87BS;" _
         & "Database=MASTER;" _
         & "Integrated Security=SSPI;" _
         & "DataTypeCompatibility=80;" _
         & "User ID=sa;" _
         & "Password=<redacted>;"

    objCon.ConnectionString = objConConnectionString
    objCon.Open        
    MsgBox "Connection opened"

    With objCom
        .CommandText = "TestProc"     'Name of the stored procedure
        .CommandType = adCmdStoredProc  'Type : stored procedure
        .ActiveConnection = objCon.ConnectionString
    End With

    Set objPara = objCom.CreateParameter("time1", adVarChar, adParamInput, 50, "02:02:02.3456123")
    Set objpara2 = objCom.CreateParameter("datetime", adDate, adParamInputOutput, , "2011-01-01 11:01:01")

    objCom.Parameters.Append objPara
    objCom.Parameters.Append objpara2

    Set objRS = objCom.Execute        
    objRS.Open
    Do While Not objRS.EOF
        For k = 0 To objRS.Fields.Count - 1
            Debug.Print objRS(k).Name & ": " & objRS(k).Value
        Next
        objRS.MoveNext
    Loop        
    ...

---

According to this, adDBTimeStamp

---

Apparently ADO is removing the milliseconds. http://support.microsoft.com/kb/246438

I have not tried this but I think you should change the data type for your parameter in the stored procedure to varchar(23) and use a string parameter that looks like this '2011-05-17T10:18:54.293'.

---

What happens when you include a T in the string value between the date and time components?
(like 2011-01-01T11:01:01.123)

Set objpara2 = objCom.CreateParameter("datetime", adDate, adParamInputOutput,,
 "2011-01-01T11:01:01.123")