title: SQL - Date Query Issue - varchar to datetime conversion resulted in out-of-range value
body: <p>I have a SQL query written by a colleague who is no longer here. The query runs as part of an SSIS job, and as of this month has started failing with the following error:</p>
  
  <blockquote>
    <p><em>The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.</em></p>
  </blockquote>
  
  <p>The query itself is just a basic select with a where clause that looks for values in a certain time range (the date range between <code>@startdate</code> and <code>@enddate</code>) </p>
  
  <p>The code to determine the time range is below:</p>
  
  <pre><code>DECLARE     @RunDateTime datetime
  DECLARE     @RunDate datetime
  DECLARE     @StartDate datetime
  DECLARE     @EndDate datetime
  DECLARE     @Month int
  DECLARE     @Year int
  DECLARE     @strStartDate varchar(10)
  
  SET     @RunDateTime = GetDate()
  SET     @RunDate = cast(round(convert(real, @RunDateTime),0,1) as datetime)
  
  IF                  DATEPART(d, @RunDate) = 16
      BEGIN       
                      SET @StartDate =  DATEADD(d, -15, @RunDate)
                      SET @EndDate = @RunDate
      END
  ELSE
      BEGIN
                      IF      Month(@RunDate) = 1
                              SET @Month = 12
                      ELSE    
                              SET @Month = Month(@RunDate) - 1
  
                      IF      Month(@RunDate) = 1
                              SET @Year = Year(@RunDate) - 1
                      ELSE
                              SET @Year = Year(@RunDate)
  
                      SET @strStartDate = CONVERT(varchar(2), @Month)+ '/16/' + CONVERT(varchar(4), @Year)
                      SET @StartDate = CONVERT(datetime, @strStartDate, 101)
                      SET @EndDate = @RunDate
      END
  </code></pre>
  
  <p>This job runs twice a month. Once on the 16th for data from the 1st to the 15th of the month, and once on the 1st of the next month for data from the 16th to the end of the previous month.</p>
  
  <p>From what I can find online, the use of the <code>varchar</code> for <code>strStartDate</code> is the likely culprit?  I'm not familiar enough with SQL to know how to replace all that convert stuff that's going on there?  Also, is there a better way to determine the end of the month date than just getting the run time?  Any help at all would be greatly appreciated. </p>
  
  <p>(PS, we run this job on SQL Server 2008 R2)  And I checked with the DBA and he said nothing about localization or regional settings has changed on the SQL server.</p>