Hello. My dates in Base are 1 day behind what they are supposed to be. I guess, i messed them up when importing from excel, but i don’t know how. Nevertheless, it’s too late to do the importing again, so i tried to increment the values by 1 by query (date = date + 1) but got the wrong data type: java.lang.NumberFormatException. How do i do the query correctly?
Sorry but I have retracted my answer using SQL. HSQLDB v1.8 is simply not capable of date math in any easy fashion. A later version would easily utilize the DATE_ADD function in SQL. I can offer an alternative using a macro but unsure as to how comfortable you would be with this. Here is a Q&D version of the code:
Sub IncrementDateInTable
REM create statements for SQL processing
If IsNull(Thisdatabasedocument.CurrentController.ActiveConnection) then
Thisdatabasedocument.CurrentController.connect
End If
oStatement = Thisdatabasedocument.CurrentController.ActiveConnection.createStatement()
oStatement2 = Thisdatabasedocument.CurrentController.ActiveConnection.createStatement()
REM Set to Scroll_Sensitive
oStatement.ResultSetType = 1005
REM Prepare the text of the SQL
stSQL = "SELECT ""DATEID"", ""Date"" FROM ""Main"" WHERE ""Volume"" = 'Forthcoming' AND ""Date"" IS NOT NULL"
REM Get the first record returned
oResult = oStatement.executeQuery(stSQL)
REM Check if record set returned
bCursorTest = oResult.first
REM Exit if record set is empty
If bCursorTest = "False" Then
MsgBox "No Records Found"
Exit Sub
End If
REM Cycle through all records
oResult.beforeFirst
While oResult.next
REM Next four lines are the fields within the record returned
loID = oResult.getInt(1)
stDate = oResult.getString(2)
REM update retrieved date by 1 day
beginMonth = Month(stDate)
beginDay = Day(stDate)
beginYear = Year(stDate)
serialDate = DateSerial(beginYear, beginMonth, beginDay)
serialDate = serialDate + 1
myDate = Format(serialDate, "'YYYY-MM-DD'")
stSQL2 = "Update ""Main"" Set ""Date"" = " & myDate &" WHERE ""DATEID"" = '" & loID & "'"
oResult2 = oStatement2.executeUpdate(stSQL2)
Wend
If oResult.last Then
MsgBox "Number of Records in Update is: " & oResult.getRow()
End If
End Sub
This would need some minor modifications to work for your table - change DATEID field to name of your primary key (located in two places). This routine works from a form with a push button or run straight from the Basic IDE. Tested with my field names & all OK. Code above reflects your fields except for mentioned DATEID.
It worked once and other times it gives the same error Wrong data type: java.lang.IllegalArgumentException. I used this Update "Main" Set "Date" = CAST( YEAR( "Date" ) || '-' || MONTH( "Date" ) || '-' || DAY( "Date" ) + 1 AS DATE) WHERE "Volume" = 'Forthcoming' AND "Date" IS NOT NULL. I checked that the field format is set to “1900-01-01”
Please see edited answer. An alternative to using a macro is to convert to a split database (always recommended anyway) which will provide an upgrade in HSQLDB as well.
Also if interested in converting to split DB,my answer on Split DB setup instructions may be of help to you.