Ask Your Question

How to increment dates in Base

asked 2018-02-05 14:10:37 +0200

ferrat val gravatar image

updated 2018-02-05 20:35:30 +0200

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?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-02-05 21:11:03 +0200

Ratslinger gravatar image

updated 2018-02-06 04:38:12 +0200

@ferrat val,

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

edit flag offensive delete link more


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"

ferrat val gravatar imageferrat val ( 2018-02-05 21:38:08 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2018-02-06 01:34:27 +0200 )edit

Also if interested in converting to split DB,my answer on Split DB setup instructions may be of help to you.

Ratslinger gravatar imageRatslinger ( 2018-02-06 04:45:16 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-02-05 14:10:37 +0200

Seen: 115 times

Last updated: Feb 06 '18