Loading data into SQL Server from Excel using ADO
Posted on 2011-09-15
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?