r - Shiny app is writing dateInputs as numbers to SQL, how do I format them as dates?

Question

I am trying to create an app to track ongoing projects. There is a tab to input new projects, and one to view current projects (among other things). The problem I'm having is that when the user enters the date (using dateInput), it saves to SQL as numeric. For example, 01/21/2021 is showing up as 18648. I can't figure out how to fix this. I am guessing it's something to do with my reactive expression, but I'm not sure. Any help is appreciated! Thanks.

library(RSQLite)
library(shiny)
library(shinythemes)
library(rdrop2)
library(DT)
library(plotly)
library(DTedit)


#Path to SQLite Database
sqlitePath <- "../test.db"
#Name of table 
table <- "project"
table2 <- "clients"
fields <- c("Analyst", "Project", "Client", "Description", "Start","End","Complete")

saveData <- function(data) {
    # Connect to the database
    db <- dbConnect(SQLite(), sqlitePath)
    # Construct the update query by looping over the data fields
    query <- sprintf(
        "INSERT INTO %s (%s) VALUES ('%s')",
        table, 
        paste(names(data), collapse = ", "),
        paste(data, collapse = "', '")
    )
    # Submit the update query and disconnect
    dbGetQuery(db, query)
    dbDisconnect(db)
}

loadData <- function() {
    # Connect to the database
    db <- dbConnect(SQLite(), sqlitePath)
    # Construct the fetching query
    query <- sprintf("SELECT * FROM project")
    # Submit the fetch query and disconnect
    data1 <- dbGetQuery(db, query)
    dbDisconnect(db)
    data1
}


loadData2 <- function() {
    # Connect to the database
    db <- dbConnect(SQLite(), sqlitePath)
    # Construct the fetching query
    query <- sprintf("SELECT * FROM %s", table2)
    # Submit the fetch query and disconnect
    data2 <- dbGetQuery(db, query)
    dbDisconnect(db)
    data2
}

#load calendar data
loadData3 <- function() {
    db <- dbConnect(SQLite(), sqlitePath)
    query <- sprintf("SELECT * FROM %s", table)
    data3 <- dbGetQuery(db, query) %>%
        mutate(title = Project,
               start = Start,
               end = End,
               color = Analyst) %>%
        select(title, color, start, end)
    dbDisconnect(db)
    data3
}

ui <- function(request){
    fluidPage(
        titlePanel("Statistical Project Tracker"), 
        theme = shinytheme("united"),
        tabsetPanel(
            tabPanel("Tasks", 
                     tabsetPanel(
                         tabPanel("New Task",
                                  mainPanel(
                                      textInput("Project", "Project Name", ""),
                                      textInput("Client", "Client"),
                                      selectInput("Analyst", "Assign to:", choices =
                                                      c("Jeremy","Michael","Caleb", "Nikki", "Sarah", "Jacob")),
                                      selectInput("Complete", "Status", choices =
                                                      c("Complete","Incomplete")),
                                      dateInput("Start", "Start Date"),
                                      dateInput("End", "End Date"),
                                      textInput("Description", "Description"),
                                      actionButton("submit", "Submit"))),
                         tabPanel("Current Tasks",
                                  mainPanel(
                                      titlePanel("Double-click on a cell to edit"),
                                      dataTableOutput("responses"))),
                         tabPanel("All Tasks", 
                                  fullcalendar::fullcalendarOutput("calendar")
                                  ))),
            tabPanel("Clients",
                     tabsetPanel(
                         tabPanel("New Client"),
                         tabPanel("Existing Clients",
                            mainPanel(
                              titlePanel("Double-click on a cell to edit"),
                              DT::dataTableOutput("clients"))))),
            tabPanel("Leads")))}


server <- function(input, output, session) {
    
    # Whenever a field is filled, aggregate all form data
    mydata <- reactive({
        data <- sapply(fields, function(x) input[[x]])
        data
           
    })
    
    # Do not bookmark submit button
    setBookmarkExclude("submit")
    
    #observeEvent(input$edit1, {loadData()})
    #observeEvent(input$edit2, {loadData2()})
    
    # When the Submit button is clicked, save the form data
    observeEvent(input$submit, {
        saveData(mydata())
        paste("Your response has been saved")})
    # Show the previous responses
    # (update with current response when Submit is clicked)
    output$responses <- renderDataTable({
        datatable(loadData(), editable = 'cell')})
    output$clients <- renderDataTable({
        datatable(loadData2(), editable = 'cell')})
    output$calendar <- fullcalendar::renderFullcalendar({
        fullcalendar::fullcalendar(loadData3())
    })
}
    
shinyApp(ui, server, enableBookmarking = "server")
       

question from:https://stackoverflow.com/questions/65832427/shiny-app-is-writing-dateinputs-as-numbers-to-sql-how-do-i-format-them-as-dates

Answer 1

Waitting for answers