title: How do I pass start date and end date parameter to SQL using Excel VBA?
body: <p>I have the following code which works when I enter a customer ID from the Northwind DB example in Excel cell D2 (e.g) ALFKI:</p>
  
  <p><img src="https://mikesknowledgebase.azurewebsites.net/images/RunSPfromExcel6.jpg" alt="excel screen"></p>
  
  <p>This is my Excel VBA code which works so far:</p>
  
  <pre class="lang-vb prettyprint-override"><code>Dim con As ADODB.Connection
  Dim cmd As ADODB.Command
  Dim rs As ADODB.Recordset
  Dim WSP1 As Worksheet
  Set con = New ADODB.Connection
  Set cmd = New ADODB.Command
  Set rs = New ADODB.Recordset
  
  Application.DisplayStatusBar = True
  Application.StatusBar = "Contacting SQL Server..."
  
  ' Remove any values in the cells where we want to put our Stored Procedure's results.
  Dim rngRange As Range
  Set rngRange = Range(Cells(8, 2), Cells(Rows.Count, 1)).EntireRow
  rngRange.ClearContents
  
  ' Log into our SQL Server, and run the Stored Procedure
  con.Open "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;Trusted_Connection=Yes;"
  cmd.ActiveConnection = con
  
  ' Set up the parameter for our Stored Procedure
  ' (Parameter types can be adVarChar,adDate,adInteger)
  cmd.Parameters.Append cmd.CreateParameter("CustomerID", adVarChar, adParamInput, 10, Range("D2").Text)
  
  Application.StatusBar = "Running stored procedure..."
  cmd.CommandText = "SP_GetOrdersForCustomer"
  Set rs = cmd.Execute(, , adCmdStoredProc)
  
  ' Copy the results to cell B7 on the first Worksheet
  Set WSP1 = Worksheets(1)
  WSP1.Activate
  If rs.EOF = False Then WSP1.Cells(8, 2).CopyFromRecordset rs
  
  rs.Close
  Set rs = Nothing
  Set cmd = Nothing
  
  con.Close
  Set con = Nothing
  
  Application.StatusBar = "Data successfully updated." 
  </code></pre>
  
  <p>The stored procedure is SP_GetOrdersForCustomer which is:</p>
  
  <pre class="lang-vb prettyprint-override"><code>CREATE PROCEDURE [dbo].[SP_GetOrdersForCustomer]
  @CustomerID nchar(5)
  AS
   BEGIN
      SELECT cst.[CustomerID],
      cst.[CompanyName],
      cst.[ContactName],
      ord.[OrderID],
      ord.[EmployeeID],
      emp.[FirstName],
      emp.[LastName],
      ord.[ShippedDate],
      prd.[ProductName],
      od.[UnitPrice],
      od.[Quantity]
         FROM [Customers] cst,
           [Orders] ord,
           [Order Details] od,
           [Employees] emp,
           [Products] prd
        WHERE ord.[CustomerID] = cst.[CustomerID]
      AND emp.[EmployeeID] = ord.[EmployeeID]
      AND od.[OrderID] = ord.[OrderID]
      AND prd.[ProductID] = od.[ProductID]
      AND cst.[CustomerID] = @CustomerID
       ORDER BY cst.[CustomerID], emp.[EmployeeID], ord.[ShippedDate] DESC
      END 
  </code></pre>
  
  <p>I now need to do the stored procedure "Sales by Year" in Northwind which has two date parameters @Beginning_Date and @Ending_Date both are datetime datatypes.</p>
  
  <p>If I put the date 1996-07-01 in Cell F1 and 1996-08-01 in Cell F2 then call the parameter:</p>
  
  <pre class="lang-vb prettyprint-override"><code>' Set up the parameter for our Stored Procedure
  ' (Parameter types can be adVarChar,adDate,adInteger,etc)
  cmd.Parameters.Append cmd.CreateParameter("@Beginning_Date", adDate, adParamInput, 10, Range("F1").Text)
  cmd.Parameters.Append cmd.CreateParameter("@Ending_Date", adDate, adParamInput, 10, Range("F2").Text)
  </code></pre>
  
  <p>it does not work.</p>
  
  <p>This is the stored procedure with the dates:</p>
  
  <pre class="lang-vb prettyprint-override"><code>USE [Northwind]
  GO
  /****** Object:  StoredProcedure [dbo].[Sales by Year]    Script Date:   30/01/2017 22:04:57 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER procedure [dbo].[Sales by Year] 
  @Beginning_Date DateTime, @Ending_Date DateTime AS
  SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year
  FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
  WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date
  </code></pre>
  
  <p>And this is the Excel VBA that doesn't work. I tried to modify what I have showed above and I think I should have adDBdate instead of adDate but it fails with "error 3421 application uses a value of the wrong type for current operation":</p>
  
  <pre class="lang-vb prettyprint-override"><code>Sub Button1_Click()
  Dim con As ADODB.Connection
  Dim cmd As ADODB.Command
  Dim rs As ADODB.Recordset
  Dim WSP1 As Worksheet
  Set con = New ADODB.Connection
  Set cmd = New ADODB.Command
  Set rs = New ADODB.Recordset
  
  
  Application.DisplayStatusBar = True
  Application.StatusBar = "Contacting SQL Server..."
  
  ' Remove any values in the cells and clear a space where we want to dump and put our Stored Procedure's results.
  Dim rngRange As Range
  Set rngRange = Range(Cells(8, 2), Cells(Rows.Count, 1)).EntireRow
  rngRange.ClearContents
  
  ' Log into our SQL Server, and run the Stored Procedure
  con.Open "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;Trusted_Connection=Yes;"
  cmd.ActiveConnection = con
  
  
  ' Set up the parameter for our Stored Procedure
  ' (Parameter types can be adVarChar,adDate,adInteger,etc)
  cmd.Parameters.Append cmd.CreateParameter("@Beginning_Date", adDate, adParamInput, 10, Range("J1").Text)
  cmd.Parameters.Append cmd.CreateParameter("@Ending_Date", adDate, adParamInput, 10, Range("J2").Text)
  
  'Show some text at the bottom on the status bar
  Application.StatusBar = "Running stored procedure..."
  
  'run the stored procedure name in SQL and drop the prefix dbo. it's not needed
  cmd.CommandText = "Sales by Year"
  
  'Execute it
  Set rs = cmd.Execute(, , adCmdStoredProc)
  
  ' Copy the results to cell B7 on the first Worksheet
  Set WSP1 = Worksheets(1)
  WSP1.Activate
  If rs.EOF = False Then WSP1.Cells(8, 2).CopyFromRecordset rs
  
  'clean terminate
  rs.Close
  Set rs = Nothing
  Set cmd = Nothing
  
  'close off the connection to the SQL database
  con.Close
  Set con = Nothing
  
  'Did it work ? Then display message at the bottom status bar
  Application.StatusBar = "Data successfully updated."
  
  End Sub
  </code></pre>