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:
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
However from R this is what I get:
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
When I try and select all the data from the table, this is what I get
sqlQuery(DF_CN,'select * from dbo.history')
Error in charToDate(x) :
character string is not in a standard unambiguous format
It may to be something about the yyyy-mm-dd default format from SQL server which I can change if I use CONVERT, but this looks like a hack and the SELECT * would not work.
Is there something in R I can do to recognise the SQL Server dates?
Ben
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.
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
ODBC Driver 11 for SQL Server.
Everything looks up to date on my system.
the [date] column was of type DATE, is now DATETIME. I now get this;
sqlQuery(DF_CN,('select * from dbo.history')
Error in as.POSIXlt.character(x, tz, ...) :
character string is not in a standard unambiguous format
It looks like some disconnect between SQL server setup and R setup.
