Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Loading data into SQL Server from Excel using ADO

Posted on 2011-09-15
3
Medium Priority
?
266 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:lee_jd
3 Comments
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 1000 total points
ID: 36542789
Not sure about SQL server but Oracle often prefers a date in the unambiguous' 01-JAN-2011' format.  Might be worth a try.
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 1000 total points
ID: 36542913
Before load convert date or Excel to SQL compitible format i.e as per the SQL Culture or default 'YYYY-MM-DD' format.
0
 
LVL 2

Author Comment

by:lee_jd
ID: 36543282
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.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question