library(DBI)
library(dplyr)
library(lubridate)

# --- Connect to the database via RSQLServer and odbc --------------------------
db <- "your SQL database"
server <- "your SQL server"
conn <- dbConnect(RSQLServer::SQLServer(), server = server, database = db,
                  properties = list(user = "", password = "",
                                    useNTLMv2 = TRUE, domain = "")
)

conn2 <- dbConnect(odbc::odbc(), dsn = "")

# --- Create the test table ----------------------------------------------------
dplyr::db_drop_table(conn, "TestTable")

if (!dbExistsTable(conn, "TestTable")) {
  TestProcessStr <- "
  CREATE TABLE TestTable(
  Process_ID INT NOT NULL IDENTITY(1,1),
  Start_Dt DATE NOT NULL,
  End_Dt DATE DEFAULT '9999-12-31',
  Comment VARCHAR(30),
  PRIMARY KEY( Process_ID )
  );"

  dbExecute(conn, TestProcessStr)
} else {
  message("TestTable exists")
}

# --- Write to test table using different connections --------------------------

rowadd <- data_frame(Start_Dt = Sys.Date(), End_Dt = Sys.Date() + months(3),
                     Comment = "SQLServer, Date as Date")

write_res <- dbWriteTable(conn, name = "TestTable",
                          value = rowadd, append = T)

# Convert all dates to character
rowadd <- rowadd %>% mutate_if(is.Date, as.character) %>%
  mutate(Comment = "SQLServer, Date as Char")

write_res <- dbWriteTable(conn, name = "TestTable",
                          value = rowadd, append = T)

rowadd <- data_frame(Start_Dt = Sys.Date(), End_Dt = Sys.Date() + months(3)) %>%
  mutate(Comment = "ODBC, Date as Date")

write_res <- dbWriteTable(conn2, name = "TestTable",
                          value = rowadd, append = T)


# Convert all dates to character
rowadd <- rowadd %>% mutate_if(is.Date, as.character) %>%
  mutate(Comment = "ODBC, Date as Character")

write_res <- dbWriteTable(conn2, name = "TestTable",
                          value = rowadd, append = T)


# View database status
ttab <- dbReadTable(conn, "TestTable")
ttab

# --- Disconnect ---------------------------------------------------------------

dbDisconnect(conn)
dbDisconnect(conn2)

Process_ID   Start_Dt     End_Dt                 Comment
1          1 2018-01-14 2018-04-14 SQLServer, Date as Date
2          2 2018-01-15 2018-04-15 SQLServer, Date as Char
3          3 2018-01-15 2018-04-15      ODBC, Date as Date
4          4 2018-01-15 2018-04-15 ODBC, Date as Character