Probably not the best, but here: con <- odbcConnect("BMC"); timezone <- Sys.timezone(); # query=paste0("select CONVERT(smalldatetime,Int_Start_Date,11) as Int_Start_Date,", " CONVERT(smalldatetime,CASE WHEN Int_Start_Time is NULL then '00:00' ", "else LEFT(Int_Start_Time,2)+':'+SUBSTRING(Int_Start_Time,3,2) end +", "':00', 14) as Int_Start_Time", ", Int_duration, RTRIM(INTTYPE) AS INTTYPE", ", RTRIM(Int_descr) AS Int_descr", ", RTRIM(INTSUBT) as INTSUBT", ", INDEXX, RTRIM(Label) AS Label", ", RTRIM(CHANGED) AS CHANGED", ", RTRIM(ALERT) AS ALERT", ", RTRIM(RELEASE) AS RELEASE", " FROM CPINTVL where Int_Start_Date BETWEEN '", startDateChar,"' and '",endDateChar,"'", "AND INTTYPE='M'" ); cpintvl <- sqlQuery(con, query, stringsAsFactors=FALSE, as.is=TRUE); # # properly combine start date and time because I couldn't figure out how to # get MS-SQL to do it for me. cpintvl$Int_Start <- strptime(paste0(substr(cpintvl$Int_Start_Date,1,11),
substr(cpintvl$Int_Start_Time,12,19)),"%Y-%m-%d %H:%M:%S"); So the actual value was created with the strptime() call at the end. The rest is just in character form. The Int_Start_Date in the DB is in yy/mm/dd format as a character field. Int_Start_Time is HHMM as a character field with leading zeros. The return value from the SELECT has Start_Int_Date formatted in yyyy-mm-dd as a character string. Start_Int_Time formatted in hh:mm:ss as a character string. In any case, you have accurately explained my foolishness. I keep forgetting about DST because I record _everything_ in my personal DBs in UTC. On Wed, Jul 30, 2014 at 1:23 PM, William Dunlap <wdun...@tibco.com> wrote: > I meant what R commands did you use to change the database's version > of the time/date object to the R version? > Bill Dunlap > TIBCO Software > wdunlap tibco.com > > > On Wed, Jul 30, 2014 at 11:07 AM, John McKown > <john.archie.mck...@gmail.com> wrote: >> On Wed, Jul 30, 2014 at 12:54 PM, William Dunlap <wdun...@tibco.com> wrote: >>>> I should have mentioned that I tried other time stamps, generated the >>>> same way as "q" above. >>> >>> How did you generate q and in what time zone were you? >> >> I got it from an MS-SQL data base which is maintained by some >> closed-source vendor software. But I manipulate the data in the SELECT >> before sending it to R via ODBC. I need to double check the raw data >> in the data base. >> >>> Note that 2am >>> on 9 March 2014 is when 'daylight savings time' started in the parts >>> of the US where it is observed. Does 2am exist or do we jump from >>> 1:59:59 to 3:00:00? >> >> Hum, that hadn't occurred to me. I need to see what is in the DB. >> >> But I think you have found my problem. If I force the timezone to be >> GMT, then the problem disappears. So that is what I'll do with this >> data. >> >>> >>> >>> Bill Dunlap >>> TIBCO Software >>> wdunlap tibco.com >> >> -- >> There is nothing more pleasant than traveling and meeting new people! >> Genghis Khan >> >> Maranatha! <>< >> John McKown -- There is nothing more pleasant than traveling and meeting new people! Genghis Khan Maranatha! <>< John McKown ______________________________________________ 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.