# Solved: Conversion error from string for date field

I have a form where a field Startdatum is formatted as DD-MM-YYYY, I now want to insert the data on display in the form into another table which has a similar named and formatted field The macro code I puzzled together is:

Sub insertarch
Dim DatabaseContext As Object
Dim DataSource As Object
Dim opand as object
Dim olid as object
Dim spand as integer
Dim slid as integer
Dim sstartdat as date
Dim oForm as object
Dim oConn as object
Dim oQuery as object

oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
opand = oForm.getByName("fmtPandidnr")
olid = oForm.getByName("fmtLidnr")
ostartdat=oForm.getbyname("fmtStartdat")
spand = opand.getCurrentValue()
slid = olid.getCurrentValue()
sstartdat=ostartdat.getCurrentValue()

DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("volks") Rem My DB name
oConn = DataSource.getConnection("","")
oQuery = oConn.CreateStatement()

SQL = "INSERT INTO ""Pandenarchief"" (""pandidnr"",""lididnr"",""Startdatum"") values ('" & spand & "','" & slid & "','"& sstartdat &"')"
oQuery.executeUpdate(SQL)
REM EVENT.SOURCE.ACTIVECONNECTION.Parent.flush

End Sub


When I run that I get an error: Conversion error from string '00:00:00" but as far as I can see, all involved have been defined as dates, not time or datetime. Where do I go wrong?

[Edit - Opaque] Changed code to pre-formatted text for better readability

edit retag close merge delete

I see now I forgot Dim ostartdat as object

But inserting that doesn't change the error encoutered.

( 2021-01-12 15:40:04 +0100 )edit

Sort by » oldest newest most voted

Hello,

Dates in a database (and as a Standard) are formatted as YYYY-MM-DD with the hyphens. You need to convert the String you have:

Dim sstartdat as String
sstartdat=ostartdat.getCurrentValue()
theDay = MID(sstartdat, 1, 2)
theMonth = MID(sstartdat, 4, 2)
theYear = MID(sstartdat, 7, 4)
goodDate = theYear & "-" & theMonth & "-" & theDay

more

Not certain if answers given are helping you as you may not respond - comment or marking the answer as accepted.

Difficult to keep answering questions when there is no feedback that the answer(s) provide what you need.

( 2021-01-17 18:23:38 +0100 )edit

Sorry, but applying this brought me another issue that I had to solve: how to test for a blank date field (the answer is "="--" "). Once I had that, your suggestion above solved the problem, tx. But the whole date handling is confusing in Base. On a previous problem I asked here (about substracting one year from a date field and the result got at first displayed as a number), the reaction was along "that's normal because a date is a number". I fear I'll be struggling for a while with date fields before it really sinks in.

( 2021-01-18 08:57:47 +0100 )edit