R help - Apr 2010 - Reading dates in R using SQL and otherwise (and some interesting behavior by the data editor)

Hello Everyone,
 
I am a newbie with about a month's worth of experience using R. I've
just spent a little time learning how to work with date values. Mostly, this has
involved converting text values into dates.
 
So far, I've managed to figure out how to do this in R proper using as.Date.
However, I'm still struggling with doing this using SQL and RODBC.
 
In the process of learning to create date values in R proper, I noticed some
interesting behavior on the part of the data editor. At first, this led me to
believe that my efforts had been unsuccessful. The output from my R console
below illustrates this behavior.
 
> test <- mydata
> test$test_date <- as.Date(test$ae_datestarted,
format='%m/%d/%Y')
> class(test$test_date)
[1] "Date"
> mode(test$test_date)
[1] "numeric"
> 
> fix(test)
 
(At this point, I clicked on the test_date column)

Warning: class discarded from column ‘test_date’
> 
> class(test$test_date)
[1] "character"
> mode(test$test_date)
[1] "character"
> 
 
When I run my code, it works correctly. But when I open the data frame in the
data editor and click on the test_date column, the editor says that it is
character. And beyond that, the editor discards the class for test_date. Should
the editor do this? Or is it my fault for trying to look at test_date in the
editor in the first place? In SAS, I'm used to creating data and then
opening the dataset to look at what I've done. Maybe I shouldn't be
doing this in R though.
 
Returning to the issue of converting text values to dates using SQL (Server) and
RODBC. Does anyone know how ot do this? I've been trying to do this using
things like Cast and Convert. Usually, these attempts fail. When SQL Server does
seem to be sending something back, it appears that R cannot accept it. Any help
with this problem would be greatly appreciated.
 
Thanks,
 
Paul     
 


      __________________________________________________________________
[[elided Yahoo spam]]
avourite sites. Download it now

	[[alternative HTML version deleted]]

If you want to look at the dataframe, then consider using View.

On Thu, Apr 8, 2010 at 11:14 AM, Paul Miller <pjmiller_57@yahoo.com>
wrote:
> Hello Everyone,
>
> I am a newbie with about a month's worth of experience using R.
I've just
> spent a little time learning how to work with date values. Mostly, this has
> involved converting text values into dates.
>
> So far, I've managed to figure out how to do this in R proper using
> as.Date. However, I'm still struggling with doing this using SQL and
RODBC.
>
> In the process of learning to create date values in R proper, I noticed
> some interesting behavior on the part of the data editor. At first, this
led
> me to believe that my efforts had been unsuccessful. The output from my R
> console below illustrates this behavior.
>
> > test <- mydata
> > test$test_date <- as.Date(test$ae_datestarted,
format='%m/%d/%Y')
> > class(test$test_date)
> [1] "Date"
> > mode(test$test_date)
> [1] "numeric"
> >
> > fix(test)
>
> (At this point, I clicked on the test_date column)
>
> Warning: class discarded from column ‘test_date’
> >
> > class(test$test_date)
> [1] "character"
> > mode(test$test_date)
> [1] "character"
> >
>
> When I run my code, it works correctly. But when I open the data frame in
> the data editor and click on the test_date column, the editor says that it
> is character. And beyond that, the editor discards the class for test_date.
> Should the editor do this? Or is it my fault for trying to look at
test_date
> in the editor in the first place? In SAS, I'm used to creating data and
then
> opening the dataset to look at what I've done. Maybe I shouldn't be
doing
> this in R though.
>
> Returning to the issue of converting text values to dates using SQL
> (Server) and RODBC. Does anyone know how ot do this? I've been trying
to do
> this using things like Cast and Convert. Usually, these attempts fail.
> When SQL Server does seem to be sending something back, it appears that R
> cannot accept it. Any help with this problem would be greatly appreciated.
>
> Thanks,
>
> Paul
>
>
>
>      __________________________________________________________________
> [[elided Yahoo spam]]
> avourite sites. Download it now
>
>        [[alternative HTML version deleted]]
>
>
> ______________________________________________
> 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<http://www.r-project.org/posting-guide.html>
> and provide commented, minimal, self-contained, reproducible code.
>
>

-- 
Jim Holtman
Cincinnati, OH
+1 513 646 9390

What is the problem that you are trying to solve?

	[[alternative HTML version deleted]]

I can use as.Date() on the result from an sql date field, but this may depend on
the backend database engine too. There may also be some sensitivity to character
set encoding used on the database and the client, and from you email it looks a
bit like you could have some problems in this respect.

Paul Gilbert
>-----Original Message-----
>From: r-help-bounces at r-project.org [mailto:r-help-bounces at
r-project.org]
>On Behalf Of Paul Miller
>Sent: April 8, 2010 11:14 AM
>To: r-help at r-project.org
>Subject: [R] Reading dates in R using SQL and otherwise (and
>someinteresting behavior by the data editor)
>
>Hello Everyone,
>?
>I am a newbie with about a month's worth of experience using R. I've
>just spent a little time learning how to work with date values. Mostly,
>this has involved converting text values into dates.
>?
>So far, I've managed to figure out how to do this in R proper using
>as.Date. However, I'm still struggling with doing this using SQL and
>RODBC.
>?
>In the process of learning to create date values in R proper, I noticed
>some interesting behavior on the part of the data editor. At first, this
>led me to believe that my efforts had been unsuccessful. The output from
>my R console below illustrates this behavior.
>?
>> test <- mydata
>> test$test_date <- as.Date(test$ae_datestarted,
format='%m/%d/%Y')
>> class(test$test_date)
>[1] "Date"
>> mode(test$test_date)
>[1] "numeric"
>>
>> fix(test)
>?
>(At this point, I clicked on the test_date column)
>
>Warning: class discarded from column ???test_date???
>>
>> class(test$test_date)
>[1] "character"
>> mode(test$test_date)
>[1] "character"
>>
>?
>When I run my code, it works correctly. But when??I open the data frame
>in the data editor and click on the test_date column, the editor says
>that it is character. And beyond that, the editor discards the class for
>test_date. Should the editor do this???Or is it my fault for trying to
>look at test_date in the editor in the first place? In SAS, I'm used to
>creating data and then opening the dataset to look at what I've done.
>Maybe I shouldn't be doing this in R though.
>?
>Returning to the issue of converting text values to dates using SQL
>(Server) and RODBC. Does anyone know how ot do this? I've been trying to
>do this using things like Cast and Convert. Usually, these attempts
>fail. When??SQL Server does seem to be sending something back, it
>appears that R cannot accept it. Any help with this problem would be
>greatly appreciated.
>?
>Thanks,
>?
>Paul?????????
>?
>
>
>      __________________________________________________________________
>[[elided Yahoo spam]]
>avourite sites. Download it now
>
>	[[alternative HTML version deleted]]
===================================================================================
La version fran?aise suit le texte anglais.

------------------------------------------------------------------------------------

This email may contain privileged and/or confidential information, and the Bank
of
Canada does not waive any related rights. Any distribution, use, or copying of
this
email or the information it contains by other than the intended recipient is
unauthorized. If you received this email in error please delete it immediately
from
your system and notify the sender promptly by email that you have done so. 

------------------------------------------------------------------------------------

Le pr?sent courriel peut contenir de l'information privil?gi?e ou
confidentielle.
La Banque du Canada ne renonce pas aux droits qui s'y rapportent. Toute
diffusion,
utilisation ou copie de ce courriel ou des renseignements qu'il contient par
une
personne autre que le ou les destinataires d?sign?s est interdite. Si vous
recevez
ce courriel par erreur, veuillez le supprimer imm?diatement et envoyer sans
d?lai ?
l'exp?diteur un message ?lectronique pour l'aviser que vous avez ?limin?
de votre
ordinateur toute copie du courriel re?u.