asked on

Loading data into SQL Server from Excel using ADO

I'm trying to load data into SQL server from Excel using ADO to run a stored procedure. I'm passing parameters into the SP that will be inserted into the table.

I have it working okay except for a cell I have formatted as a date on the worksheet.

The VBA I'm using is :

cmd.Parameters.Append cmd.CreateParameter("TargetDate", adDate, adParamInput, Target_Date, 8)

Target_Date is defined as DATE in VBA.

The field in SQL server is of type Datetime

The parameter in th stored procedure is of type : Datetime

I think the ADO data type adDate is different to sql Datetime. When I load data such as "01/01/2011"

It appears as 1900-01-07 00:00:00.000 in SQL Server.

Any ideas on how I can convert the Excel date to a date that is compatible with ADO & SQL Server?

ASKER CERTIFIED SOLUTION

andrewssd3

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Alpesh Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lee_jd

ASKER

The only way I can pass yyyy-mm-dd is be changing my Excel type to String.

When I pass this in to the parameter I get a type mismatch error.

If I change the parameter command to :

cmd.Parameters.Append cmd.CreateParameter("TargetDate", adVarChar, adParamInput, Target_Date, 8)

I also get a type mismatch error.