adDBTimeStamp - Access VBA to SQL Server Stored Procedure

Question

I am looking to pass a date variable from Access to a SQL Server stored procedure. Please see my code:

VBA:

searchDate = 03/07/2014
cmd.Parameters.Append _
        cmd.CreateParameter("@searchDate", adDBTimeStamp, adParamInput, , searchDate)

searchDate is a date formatted as MM/DD/YYYY

SQL:

ALTER PROCEDURE [dbo].[spAppendActivity]
    @searchDate as datetime,
AS
   SET NOCOUNT ON;

   delete * from tbl_activity_losses;

   select [Date] 
   into tbl_activity_losses 
   from tbl_master_rec 
   where [Date] = @searchDate

I get an ODBC error stating

[Microsoft][ODBC SQL Server Driver] Conversion failed when converting date and/or time from character string.

Any help on how to pass this date field to my stored procedure would be greatly appreciated.

Try printing out the value of @searchDate in your sproc. I bet it will be very different from what you think you are sending. — crthompson
– crthompson, Commented Jun 2, 2014 at 21:36
It is in the format (yyyymmddhhmmss plus a fraction in billionths). I am not sure how to alter my code to pass a value like this. Do you have any ideas? — user2989011
– user2989011, Commented Jun 2, 2014 at 21:39
I tried using adbdDate and it returns the error "[Microsoft][ODBC SQL Server Driver] Optional Feature Not Implemented. " I have not found a different way to pass a date variable. Do you have any suggestions? — user2989011
– user2989011, Commented Jun 2, 2014 at 21:43
I see that you've posted this question before. Did adVarChar work? — crthompson
– crthompson, Commented Jun 2, 2014 at 21:54

crthompson · Accepted Answer · 2014-06-02 21:56:45Z

3

The problem is adDBTimeStamp. It is converting your simple date string into a timestamp. (NOT a date)

When that timestamp is passed to your stored procedure, SQL server has no idea how to turn that long string into a date.

Changing the type from adDBTimeStamp to adVarChar will keep it as a string and SQL Server will know how to parse it properly.

Additionally, you can change your stored procedure type from DateTime to varchar as well. SQL Server will know how to parse that into a date.

edited Jun 2, 2014 at 21:56

answered Jun 2, 2014 at 21:50

crthompson

15.9k6 gold badges64 silver badges87 bronze badges

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

1 Comment

user2989011 Over a year ago

When I try to pass it as varchar, I get the following ERROR: Conversion failed when converting date and/or time from character string."

Collectives™ on Stack Overflow

adDBTimeStamp - Access VBA to SQL Server Stored Procedure

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related