0

I have this database on SQL Server. When I run the following query:

SELECT * FROM myTable WHERE myDateField <= '2014-12-31'

I get 51618 rows.

But when I run the exact same query from R, using RODBC, I get "<0 rows> (or 0-length row.names)".

Now let's say I change the date:

SELECT * FROM myTable WHERE myDateField <= '2010-01-01'

Then I get the exact same results both on SQL Server directly and on R (!?).

I've found this answer and so I tried adding believeNRows = FALSE and rows_at_time = 1 to my odbcDriverConnect statement. But that didn't change anything.

So, what could possibly be going on here? I've been at this for almost 24 hours now and I'm completely out of ideas.

Client: CentOS 7.1.1503, R 3.2.1, RODBC 1.3-12

Server: Windows Server 2012, SQL Server 2014

Community
  • 1
  • 1
Parzival
  • 2,004
  • 4
  • 33
  • 47
  • 1
    Just to try stuff... Can you make a view on the server that is defined by your desired query and then run `select * from your_view` in R? Another thing to do would be to keep moving your `2010-01-01` closer to `2014-12-31` to see where the breakdown occurs. – Dean MacGregor Nov 10 '15 at 17:27
  • Ha! That did it. I kept moving the date closer and closer to 2014-12-31, as you suggested, and so I found out that the problem is in the formatting: somehow the dates are interpreted as yyyy-dd-mm when SQL Server receives the query from RODBC. Many thanks! Now on to figuring out why this is happening. – Parzival Nov 10 '15 at 17:59
  • 1
    https://msdn.microsoft.com/en-us/library/ms189491.aspx – Dean MacGregor Nov 10 '15 at 18:08
  • Solved! If you want to turn your comment into an answer I'll accept it, so you get proper credit. – Parzival Nov 10 '15 at 19:12

1 Answers1

2

The first thing you can do is to change the working date incrementally until you discover a pattern that causes the query to fail. Perhaps (for future readers) the problem is that SQL Server is expecting a format other than the standard. If that's the case then a query for the first day of the first month would (by coincidence only) return the proper result. You can use set dateformat to get SQL Server to accept the format you need.

Dean MacGregor
  • 11,847
  • 9
  • 34
  • 72