title: Msg 8114, Level 16, State 5, Procedure sp_product_listing, Line 0 Error converting data type varchar to datetime
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/9778381/sql-msg-8114-level-16-state-5-procedure-sp-product-listing-line-0-error-conv">SQL Msg 8114, Level 16, State 5, Procedure sp_product_listing, Line 0 Error converting data type varchar to datetime</a>  </p>
  </blockquote>
  
  
  
  <p>I've seen that this was asked a while ago by someone in another semester at my school, haha. But the answer they got didn't really satisfy me. Here is the link to the other question: <a href="https://stackoverflow.com/questions/9778381/sql-msg-8114-level-16-state-5-procedure-sp-product-listing-line-0-error-conv">SQL Msg 8114, Level 16, State 5, Procedure sp_product_listing, Line 0 Error converting data type varchar to datetime</a>. </p>
  
  <p>Most of what the answer says makes not much sense to me as I am very new to SQL. If we could open up another discussion on this, it would be great. Especially minus the teacher bashing that has been going on in my questions lately...</p>
  
  <p>The following statement completes successfully, BUT:   </p>
  
  <pre><code>CREATE PROCEDURE sp_product_listing
  (
       @product varchar(30),
       @month   datetime,
       @year    datetime
  )
  AS
    SELECT
       'product_name' = products.name,
       products.unit_price,
       products.quantity_in_stock,
       'supplier_name' = suppliers.name
    FROM
       suppliers
    INNER JOIN
       products ON suppliers.supplier_id = products.supplier_id
    INNER JOIN
       order_details ON products.product_id = order_details.product_id
    INNER JOIN
       orders ON order_details.order_id = orders.order_id
    WHERE
       products.name = @product 
       AND MONTH ('orders.order_date') = @month 
       AND YEAR ('orders.order_date') = @year;
  GO
  </code></pre>
  
  <p>When I try and execute the procedure, I get an error message:</p>
  
  <blockquote>
    <p><em>Msg 8114, Level 16, State 5, Procedure sp_product_listing, Line 0<br>
    Error converting data type varchar to datetime.</em></p>
  </blockquote>
  
  <p>Here's how I execute the stored procedure:</p>
  
  <pre><code>EXECUTE sp_product_listing @product = 'Jack%', @month = 'June', @year = 2001;
  GO
  </code></pre>
  
  <p>I've tried changing the two <code>datetime</code> datatypes to varchars and that has not worked because the column itself is <code>datetime</code> datatype originally. I'm not sure what I'm missing here?</p>
  
  <p>UPDATE:</p>
  
  <p>This was the final solution as guided by <a href="https://stackoverflow.com/users/1554034/valex">https://stackoverflow.com/users/1554034/valex</a>.</p>
  
  <pre><code>CREATE PROCEDURE sp_product_listing
  (
     @product  varchar(30),
     @month    int,
     @year     int
  )
  AS
     SELECT               
        'product_name' = products.name,
        products.unit_price,
        products.quantity_in_stock,
        'supplier_name' = suppliers.name
     FROM
        suppliers
     INNER JOIN
        products ON suppliers.supplier_id = products.supplier_id
     INNER JOIN 
        order_details ON products.product_id = order_details.product_id
     INNER JOIN 
        orders ON order_details.order_id = orders.order_id
     WHERE
        products.name LIKE @product 
        AND MONTH (orders.order_date) = @month 
        AND YEAR (orders.order_date) = @year;
    GO
  </code></pre>
  
  <p>Also, for anyone still reading, here is the solution my teacher gave me which gives the same results.</p>
  
  <pre><code>CREATE PROCEDURE sp_product_listing
  (
  @product    varchar(30),
  @month      varchar(10),
  @year       char(4)
  )
  AS
      SELECT
         'product_name' = products.name,
         products.unit_price,
         products.quantity_in_stock,
         'supplier_name' = suppliers.name
      FROM
         suppliers
      INNER JOIN
         products ON suppliers.supplier_id = products.supplier_id
      INNER JOIN
         order_details ON products.product_id = order_details.product_id
      INNER JOIN
         orders ON order_details.order_id = orders.order_id
      WHERE   
         products.name LIKE @product 
         AND DATENAME(MONTH,orders.order_date) = @month 
         AND YEAR (orders.order_date) = @year;
     GO
  </code></pre>