title: RODBC management of dates from SQL server
body: <p>I have a SQL database (2012) that I am trying to access through R (R Studio). From Enterprise Manager this is what it looks like:</p>
  
  <pre><code>select top 5[date],value from dbo.history
  
  1991-02-11  11.1591
  
  1991-02-12  11.2
  
  1991-02-13  11.3501
  
  1991-02-14  11.37
  
  1991-02-15  11.3002
  </code></pre>
  
  <p>However from R this is what I get:</p>
  
  <pre><code>sqlQuery(DF_CN,'select top 5 [date],value from dbo.history')
           date value
  1  0011-02-19 11.16
  
  2  0012-02-19 11.20
  
  3  0013-02-19 11.35
  
  4  0014-02-19 11.37
  
  5  0015-02-19 11.30
  </code></pre>
  
  <p>When I try and select all the data from the table, this is what I get</p>
  
  <pre><code>sqlQuery(DF_CN,'select * from dbo.history')
  Error in charToDate(x) : 
    character string is not in a standard unambiguous format
  </code></pre>
  
  <p>It may to be something about the yyyy-mm-dd default format from SQL server which I can change if I use <code>CONVERT</code>, but this looks like a hack and the <code>SELECT *</code> would not work.</p>
  
  <p>Is there something in R I can do to recognise the SQL Server dates?</p>
  
  <p>Ben</p>
  
  <hr>
  
  <p>This is driving me nuts - surely someone has seen this before - there is a clear disconnect between the SQL Server output and what R is reading. </p>
  
  <p>I am using RStudio 0.98.1091 and R x64 3.1.2.
  Sql Server 2014
  Microsoft SQL Server Management Studio                      12.0.2000.8
  Microsoft Data Access Components (MDAC)                     6.1.7601.17514
  Microsoft MSXML                                             3.0 4.0 6.0 
  Microsoft Internet Explorer                                 9.10.9200.17148
  Microsoft .NET Framework                                    4.0.30319.18444
  Operating System                                            6.1.7601</p>
  
  <p>ODBC Driver 11 for SQL Server.</p>
  
  <p>Everything looks up to date on my system.</p>
  
  <p>the <code>[date]</code> column was of type <code>DATE</code>, is now <code>DATETIME</code>. I now get this;</p>
  
  <pre><code>sqlQuery(DF_CN,('select * from dbo.history')
  
   Error in as.POSIXlt.character(x, tz, ...) : 
    character string is not in a standard unambiguous format 
  </code></pre>
  
  <p>It looks like some disconnect between SQL server setup and R setup. </p>