title: sql server: 'Conversion failed when converting date and/or time from character string using'
body: <p>I have come up with a code from the previous developers about a code wherein the date from the database saved was like </p>
  
  <pre><code>'Jan 1 2018 12:01PM'
  </code></pre>
  
  <p>Now there is a stored procedure for report output that gets the range of dates,</p>
  
  <p>the datatype is </p>
  
  <pre><code>@datefrom varchar(50),
  @dateto varchar(50)
  </code></pre>
  
  <p>the statement is like this</p>
  
  <pre><code> where 
  (CAST(ai.date_created as DATETIME2) between @datefrom and @dateto) AND
  ai.sex = (CASE WHEN @sex = 'ALL' THEN ai.sex ELSE @sex END) AND
  ai.special_health_condition = (CASE WHEN @special = 'ALL' THEN ai.special_health_condition ELSE @special END)
  END
  </code></pre>
  
  <p>when code is executed this error shows</p>
  
  <pre><code>Conversion failed when converting date and/or time from character string
  </code></pre>
  
  <p>i tried to change the format of ai.date_created based on <a href="https://stackoverflow.com/questions/14119133/conversion-failed-when-converting-date-and-or-time-from-character-string-while-i">this</a> answer but nothing seems to work. What is wrong here? Sorry, I am a newly graduate student and this came like a puzzle to me.  Thanks in advance.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>The code goes like this:</p>
  
  <pre><code>    @datefrom date,
      @dateto date,
      @sex varchar(50),
      @special varchar(50)
  AS
  BEGIN
          select 
      ai.application_no,
      ai.franchise_no,
      ai.operator_name,
      ai.full_address,
      m.motor_no,
      ai.date_created
  ,ai.frist_name,
  ai.middle_name,
  ai.surname,
  ai.citizenship,
  ai.primary_occupation,
  ai.contact_no,
  ai.email_add,
  ai.street,
  ai.subdivision,ai.barangay,
  ai.city,
  ai.sex,
  ai.civil_status,
  ai.type,
  ai.type_of_ownership,
  m.chassis_no,
  m.year_model,
  m.plate_no,
  b.brand_name,
  ai.tax_certification_issued_on,
  ai.tax_certification_no,
  m.toda_id
  ,ai.age,
   ai.special_health_condition
  
  from vfTA_tblApplicationInfo as ai inner join 
  vfTA_tblMotor as m on m.motor_id = ai.motor_id inner join
  vfTA_tblBrand as b on b.brand_id = m.brand_id 
  
  where 
  (CAST(ai.date_created as DATETIME2) between @datefrom and @dateto) AND
  ai.sex = (CASE WHEN @sex = 'ALL' THEN ai.sex ELSE @sex END) AND
  ai.special_health_condition = (CASE WHEN @special = 'ALL' THEN ai.special_health_condition ELSE @special END)
  END
  </code></pre>
  
  <p>The data that goes are datefrom = 'Jan 1 2018 12:00AM', dateto = 'Jan 31 2018 11:59PM', sex = 'All', special = 'ALL'</p>
  
  <p>This is the code the previous developers left. Thanks in advance.</p>