title: sqldf in R returns NA for all rows in SQL CASE statement
body: <p>I have a dataframe in R called "<code>data</code>". The <code>EventDate</code> column is a <code>date format</code> and the rest are double or character fields. </p>
  
  <p>I'm trying to pull most of the columns from "<code>data</code>", but <code>aggregate</code> the <code>EventDate</code> values into year-month designations instead of the current year-month-day designations (so I want to end up with 01-2018, 02-2018, 03-2018, etc as the row values). </p>
  
  <p>I was having a lot of trouble doing this in base R or finding any packages to do it easily. I have a lot more experience in SQL than R so I opted to use the sqldf package.</p>
  
  <p>Here is the code I wrote:</p>
  
  <pre><code>newdata &lt;- sqldf("SELECT ID1, ID2, ID3, Staff, Type, POC, TOC1, TOC2, TOC3, CASE
    WHEN EventDate &gt;= '2018-01-01' AND EventDate &lt; '2018-02-01' THEN 'Jan 2018'
    WHEN EventDate &gt;= '2018-02-01' AND EventDate &lt; '2018-03-01' THEN 'Feb 2018'
    WHEN EventDate &gt;= '2018-03-01' AND EventDate &lt; '2018-04-01' THEN 'Mar 2018'
    WHEN EventDate &gt;= '2018-04-01' AND EventDate &lt; '2018-05-01' THEN 'Apr 2018'
    WHEN EventDate &gt;= '2018-05-01' AND EventDate &lt; '2018-06-01' THEN 'May 2018'
    WHEN EventDate &gt;= '2018-06-01' AND EventDate &lt; '2018-07-01' THEN 'Jun 2018'
    WHEN EventDate &gt;= '2018-07-01' AND EventDate &lt; '2018-08-01' THEN 'Jul 2018'
    WHEN EventDate &gt;= '2018-08-01' AND EventDate &lt; '2018-09-01' THEN 'Aug 2018'
    WHEN EventDate &gt;= '2018-09-01' AND EventDate &lt; '2018-10-01' THEN 'Sep 2018'
    WHEN EventDate &gt;= '2018-10-01' AND EventDate &lt; '2018-11-01' THEN 'Oct 2018'
    WHEN EventDate &gt;= '2018-11-01' AND EventDate &lt; '2018-12-01' THEN 'Nov 2018'
    WHEN EventDate &gt;= '2018-12-01' AND EventDate &lt; '2019-01-01' THEN 'Dec 2018'
    END AS EventMonth
    FROM data
    ORDER BY EventMonth, Staff, ID1")
  </code></pre>
  
  <p>The code executes without any issues and produces mostly correct results, only that it returns <code>NA</code> for all rows in the <code>EventMonth</code> column of "<code>newdata</code>" instead of <code>"Jan 2018", "Feb 2018"</code>, etc.</p>
  
  <p>It might be a formatting problem with the <code>EventDate</code> column in "<code>data</code>", but <code>data$EventDate</code> is listed as a date type in RStudio and it's in the <code>yyyy-mm-dd format</code>. </p>
  
  <p>I also tried running the following (which doesn't error out or produce any warnings) before running the previous code as a sanity check, but it still gave me a column full of NAs for EventMonth:</p>
  
  <pre><code>data$EventDate &lt;- as.Date(data$EventDate)
  </code></pre>
  
  <p>The SQL produces the expected result when I run it in SQL Server (there are no NAs in the EventMonth column), but I unfortunately won't always have access to SQL Server going forward, which is why I'm bothering with R at all. </p>
  
  <p>I need a way to do this without an actual SQL database engine in order to perform similar aggregations in the future. I recently installed <code>R</code>, <code>RStudio</code>, and the <code>sqldf</code> package within the last month so I shouldn't be using anything that's horribly outdated.</p>
  
  <p>Is there a way to get my desired result using <strong>sqldf, base R, or some other R package</strong>?</p>