Android Question Inserting Dates into MSAccess

BigBoss123

Member
Licensed User
Longtime User
I'm sure this has been covered before but I could not find it in the forum, nor a google search has helped my much.

I can read dates from the Database as they are stored in ticks, and this can be solved when putting them into a table, etc.

My problem is when I want to INSERT INTO Table1 a Date.
I can put the date into ticks with DateTime.DateParse(txtDate.Text) but using jRDC.insertRecord the jRDCServer response is

<title>Error 500 org.hsqldb.HsqlException: data exception: invalid datetime format: java.text.ParseException: Unparseable date: &quot;1620252000000&quot;</title>

Can anyone help me to solve this problem. I prefer to keep the dates in the database as DATE/TIME and not convert to TEXT as there is a lot of calculating using the dates.

Thanks ever so much
 

OliverA

Expert
Licensed User
Longtime User
You may need to convert your ticks to a valid access datetime string before submitting it to jRDC. The reason for this is that underneath the hood (in Java land), the SQL library uses setObject to set the parameter values of a parameterized query instead of setTimestamp

B4X:
    Dim formatBackup As String = DateTime.DateFormat
    DateTime.DateFormat = "#MM/dd/yyyy hh:mm:ss a#"
    'If the above is the wrong format (the insert bombs out), could also try
    'DateTime.DateFormat = "MM/dd/yyyy hh:mm:ss a"
    'or
    'DateTime.DateFormat = "MM/dd/yyyy HH:mm:ss"
    Dim theTimeInTicks As Long = DateTime.Now
    Dim timeStringForAccess As String = DateTime.Date(theTimeInTicks)
    Log(timeStringForAccess) ' use timeStringForAccess as the datetime parameter for your query
    DateTime.DateFormat = formatBackup
 
Upvote 0
Top