Ask Your Question
0

Solved: Conversion error from string for date field

asked 2021-01-12 15:34:47 +0100

hermanv gravatar image

updated 2021-01-18 08:58:20 +0100

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 flag offensive close merge delete

Comments

I see now I forgot Dim ostartdat as object

But inserting that doesn't change the error encoutered.

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

1 Answer

Sort by » oldest newest most voted
0

answered 2021-01-12 20:09:40 +0100

Ratslinger gravatar image

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
edit flag offensive delete link more

Comments

@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.

Ratslinger gravatar imageRatslinger ( 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.

hermanv gravatar imagehermanv ( 2021-01-18 08:57:47 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-01-12 15:34:47 +0100

Seen: 33 times

Last updated: 18 hours ago