• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

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?
2 Solutions
Not sure about SQL server but Oracle often prefers a date in the unambiguous' 01-JAN-2011' format.  Might be worth a try.
Alpesh PatelAssistant ConsultantCommented:
Before load convert date or Excel to SQL compitible format i.e as per the SQL Culture or default 'YYYY-MM-DD' format.
lee_jdAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now