I have a SQL database (2012) with which I am trying to access through R (R Studio). From Enterprise Manager, it looks like this:
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 to select all the data from a 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 could be something like the default format yyyy-mm-dd from the SQL server, which I can change if I use CONVERT , but it looks like a hack and SELECT * will not work.
Is there anything in R that I can do to find out SQL Server dates?
Ben
It drives me crazy - someone must have seen this before - there is a clear disconnect between SQL Server output and reading R.
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 11 driver for SQL Server.
Everything looks in my system.
the [date] column was of type DATE , now DATETIME . Now I 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
There are some connections between installing SQL Server and installing R.