Greetings:
I am trying to query an MSSQL database which contains time series data
using RODBC.
Using Server Management Studio, I can retrieve the data with this query:
Select * from tb_ace_mag_1m where time_tag>='2012-01-08 00:00:00' AND
time_tag<'2012-01-08 03:00:00'
However, when I try to accomplish this using R:
sqlQuery(channel1,"Select * from tb_ace_mag_1m where
time_tag>='2012-01-08 00:00:00' AND time_tag<'2012-01-08 03:00:00'", max=10)
I obtain no results:
[1] time_tag insert_time dsflag numpts gse_bx gse_by
gse_bz gse_lat gse_lon gsm_bx gsm_by gsm_bz
gsm_lat gsm_lon bt
<0 rows> (or 0-length row.names)
If I omit the time_tag parameter, I get the following:
> sqlQuery(channel1,"select * from tb_ace_mag_1m", max=10)
time_tag insert_time dsflag numpts gse_bx
gse_by gse_bz gse_lat gse_lon gsm_bx gsm_by
gsm_bz gsm_lat gsm_lon bt
1 2013-01-21 15:23:00 2013-01-21 15:28:26 0 37 0.34268624
-2.814154 -0.460940033 -9.2350445 276.9429 0.33351120 -2.713921
-0.8790606 -17.822054 277.0059 2.872170
2 2013-01-21 15:24:00 2013-01-21 15:29:29 0 60 0.45056427
-2.862180 -0.404557437 -7.9486165 278.9461 0.44123372 -2.769705
-0.8321871 -16.526619 279.0516 2.925534
3 2013-01-21 15:25:00 2013-01-21 15:30:00 0 15 0.16728164
-2.787083 -1.013695598 -19.9538670 273.4348 0.15818150 -2.602005
-1.4240593 -28.647099 273.4789 2.970420
How do I properly format the query in RODBC to obtain the results I seek?
I was unable to discover a solution in the archives, although it appears
I'm not the only one who has struggled with date-time queries.
Thanks,
Rob Steenburgh
NOAA/NWS Space Weather Prediction Center
______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
