title: SQL Procedure failing due to Conversion of Varchar To DateTime error
body: <p>Im having an issue with a Stored Procedure I have written, I can call it in management studio and it works fine, yet calling it from my codebehind gives this error <code>The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.</code> when i call da.Fill(dt)</p>
  
  <p>What is really puzzling me here is that I dont actually convert anything from varchar to datetime, the most I do with the date variables is format them into a different datetime format.</p>
  
  <p>My VB.Net code</p>
  
  <pre><code>  Private Function dbGetEvents(ByVal start As DateTime, ByVal days As Integer) As DataTable
        Dim dt As New DataTable()
  
        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("Blueprint").ToString())
  
        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "spGetEventDetails"
        cmd.Parameters.AddWithValue("@start", start)
        cmd.Parameters.AddWithValue("@end", start.AddDays(days))
        cmd.Parameters.AddWithValue("@userid", ddlUser.SelectedValue)
  
        cmd.Connection = conn
  
        Using da As New SqlDataAdapter(cmd)
           conn.Open()
           da.Fill(dt)
           conn.Close()
        End Using
  
        Return dt
     End Function
  </code></pre>
  
  <p>My SQL Procedure:</p>
  
  <pre><code>CREATE PROCEDURE spGetEventDetails
  (
      @start AS DATETIME,
      @end AS DATETIME,
      @userid AS INT
  )
  AS
  BEGIN
  
  SET @start = CONVERT(CHAR(10), CAST(@start AS DATETIME), 23)
  SET @end = CONVERT(CHAR(10), CAST(@end AS DATETIME), 23)
  
      -- GET DETAILS FOR TICKETS
      SELECT
          [ev].[id]                           AS [EventID],
          CAST([tck].[TicketID] AS NVARCHAR)  AS [TicketID],
          [tck].[Description]                 AS [Description],
          [ev].[eventstart]                   AS [Start],
          [ev].[eventend]                     AS [End],
          [ev].[status]                       AS [Status],
          [ev].[type]                         AS [EventType],
          CAST([tck].[Type] AS NVARCHAR)      AS [Type],
          CAST([tck].[Product] AS NVARCHAR)   AS [Product],
          CAST([tck].[Severity] AS NVARCHAR)  AS [Severity],
          [ev].[resource_id]                  AS [resource_id],
          CAST(pers.FirstName AS NVARCHAR)    AS [FirstName],
          CAST(pers.Surname AS NVARCHAR)      AS [Surname],
          CAST(tck.LogDate AS NVARCHAR)       AS [LogDate]
      FROM
          tblSupportEvent ev
      JOIN
          tblTicketsInEvents tie
      ON
          ev.id =tie.eventID
      JOIN
          SupportTicketsTbl tck
      ON
          tie.ticketID = tck.TicketID 
      JOIN 
          PersonnelTbl pers
      ON
          pers.ID = tck.LoggedBy 
      WHERE
          ev.type = 1
      AND 
          NOT (([eventend] &lt;= @start) OR ([eventstart] &gt;= @end))  
      AND  
          resource_id &lt;&gt; 0  
      AND   
          ev.resource_id = @userid  
  
  UNION 
      -- GET THE DETAILS FOR NON TICKET ITEMS
  
      SELECT
          [ev].[id]           AS [EventID],
          ''                  AS [TicketID],
          [nti].[Description] AS [Description],
          [ev].[eventstart]   AS [Start],
          [ev].[eventend]     AS [End],
          [ev].[status]       AS [Status],
          [ev].[type]         AS [EventType],
          ''                  AS [Type],
          ''                  AS [Product],
          ''                  AS [Severity],
          [ev].[resource_id]  AS [resource_id],
          ''                  AS [FirstName],
          ''                  AS [Surname],
          ''                  AS [LogDate]
      FROM
          tblSupportEvent ev
      JOIN
          tblNonTicketsInEvents nte
      ON
          ev.id = nte.eventID 
      JOIN
          tblNonTicketItems nti
      ON
          nte.nonTicketID = nti.id    
      WHERE
          ev.type = 2
      AND
          NOT (([eventend] &lt;= @start) OR ([eventstart] &gt;= @end))  
      AND  
          resource_id &lt;&gt; 0  
      AND   
          ev.resource_id = @userid  
  END
  </code></pre>
  
  <p>I cant see where this error could be coming from, having tested running it in SQL management studio without error I am pretty sure the error is in the VB.</p>
  
  <p>My first thought was that maybe the parameter I am passing in the @start / @end values arent being recognised as DateTime so I tried adding the parameters like:</p>
  
  <pre><code> Dim p As New SqlParameter
  
    p.SqlDbType = SqlDbType.DateTime
    p.ParameterName = "@start"
    p.Value = start
    cmd.Parameters.Add(p)
  </code></pre>
  
  <p>but this just gives me the same result. If anyone can suggest where I am going wrong id be gratefull.</p>
  
  <p>Thanks</p>