Conversion error from string for date field

[hermanv]

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

[hermanv]

I see now I forgot
Dim ostartdat as object

But inserting that doesn’t change the error encoutered.

[Ratslinger]

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

[Ratslinger]

@hermanv,

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.

[hermanv]

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.

[hermanv]

I’m still struggling. When I have a date field 01/01/2018 and use your code, i added
msgbox(sstartdat,0,“Datumveld”), I get as output 43101 which I think is the number value of the date

[hermanv]

What is stranger still is that I added an if to catch empty dates:

if goodDate="--"
then sql insert without date field goodDate
else sql insert with date field goodDate

• and this last one throws conversion error.

[Ratslinger]

@hermanv,

You have not provided enough information in this post to answer the problems noted in you comments. The 43101 is the number of days since December 31, 1899 (system default). Will further address this in your other post which is related to this post → The macro below worked OK before, throws now a different error in Linux and Win 10