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

Question

The datetime data type in SQL Server 2008 supports milliseconds.

I am trying to execute a stored procedure that takes a datetime parameter with an argument with milliseconds as the input / output value.

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

I see the conversion problem in the method below.

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

What is the equivalent datatype for datetime 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 ... 

Answer 1

According to this , adDBTimeStamp

Answer 2

ADO seems to be deleting 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 one '2011-05-17T10:18:54.293' .

Answer 3

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

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