RODBC管理SQL Server中的日期 [英] RODBC management of dates from SQL server

问题描述

我有一个SQL数据库（2012），我正在尝试通过R（R Studio）访问。从企业管理器中看起来像这样：

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

但是从R来看这是我得到的是：

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

可能与SQL Server的yyyy-mm-dd默认格式有关，我可以更改如果我使用 CONVERT ，但这看起来像是骇客， SELECT * 无效。

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.

RI中是否可以识别SQL Server日期？

Is there something in R I can do to recognise the SQL Server dates?

Ben

这让我发疯了-肯定有人以前见过-SQL Server输出与R读取的内容之间存在明显的脱节。

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.

我正在使用RStudio 0.98.1091和R x64 3.1.2。
Sql Server 2014
Microsoft SQL Server Management Studio 12.0.2000.8
Microsoft数据访问组件（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
操作系统6.1.7601

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

用于SQL Server的ODBC驱动程序11。

ODBC Driver 11 for SQL Server.

我的系统上的所有内容都是最新的。

Everything looks up to date on my system.

[date] 列的类型为 DATE ，现在为 DATETIME 。我现在知道了;

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 

SQL服务器设置和R设置之间似乎有些脱节。

It looks like some disconnect between SQL server setup and R setup.

推荐答案

通过使用 as.is 参数将其删除：

I worked it out by using the as.is parameter:

sqlQuery(DF_CN,'select * from dbo.history',as.is=T)

然后投射值I直接在R中需要。

Then casting the values I needed directly in R.

这篇关于RODBC管理SQL Server中的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！