Well, I have this issue when I import the entire data set within specific date range (64468 records). When I select only 2 URNs containing this problematic date and one other URN (to have a mixture of both types), conversion is fine:
library(RODBC)
library(DBI)
abc <- odbcConnect("livestats")
data.source <- sqlQuery(abc, "SELECT * FROM LIVESTATS.HY_EURO.ALL_Sales_Data WHERE (URN='942012385480' or URN='942012508492' or URN='842011502448')")
library(dplyr)
test.data <- select(data.source, URN, InterviewDate)
# imported data frame
test.data <- data.frame(
URN = c(842011502448, 942012385480, 942012508492),
InterviewDate = c("2018-07-25 00:00:00", "2018-07-01 00:57:00",
"2018-07-01 00:35:00")
)
library(zoo)
test.data$Qtr <- as.yearqtr(test.data$InterviewDate, format = "%Y-%m-%d")
test.data
#> URN InterviewDate Qtr
#> 1 842011502448 2018-07-25 00:00:00 2018 Q3
#> 2 942012385480 2018-07-01 00:57:00 2018 Q3
#> 3 942012508492 2018-07-01 00:35:00 2018 Q3
When I import the large data set, create quarters and then subset the same URNs, quarters are wrong:
library(RODBC)
library(DBI)
abc <- odbcConnect("livestats")
data.source.all <- sqlQuery(abc, "SELECT * FROM LIVESTATS.HY_EURO.ALL_Sales_Data WHERE (InterviewDate between '01-Jul-2018 00:00:00' and '30-Sep-2019 23:59:59')")
test.data.all <- select(data.source3, URN, InterviewDate)
library(zoo)
test.data.all$Qtr <- as.yearqtr(test.data.all$InterviewDate, format = "%Y-%m-%d")
test.data.urns <- subset(test.data.all, URN==942012385480 | URN==942012508492 | URN==842011502448)
# imported data frame
test.data.urns <- data.frame(
URN = c(842011502448, 942012385480, 942012508492),
InterviewDate = c("2018-07-25 00:00:00", "2018-07-01 00:57:00",
"2018-07-01 00:35:00"),
Qtr = c("2018 Q3", "2018 Q2", "2018 Q2")
)
#> URN InterviewDate Qtr
#> 1 842011502448 2018-07-25 00:00:00 2018 Q3
#> 2 942012385480 2018-07-01 00:57:00 2018 Q2
#> 3 942012508492 2018-07-01 00:35:00 2018 Q2