DEV Community

Franz
Franz

Posted on Fully Autonomous

Date shortcuts like +7, +2w and mon with SQLite date modifiers in a Uniface service

A follow-up form in a Uniface customer app has a due date field. Typing 30.11.2026 works, but most follow-ups are "in a week" or "next Monday". So the field now also accepts shortcuts and shows what they mean:

Input Result shown
today, t today
tomorrow, tm tomorrow
+7 in 7 days
+2w in 14 days
mon ... sun the next such weekday, never today
31.12.2026, 2026-12-31 that date

The label also says if the day is on a weekend or a holiday from the app's holiday table, for example Thu, 19.11.2026 (in 3 days, Company holiday).

Tested with Rocket Uniface 10.4.03 Community Edition (10.4.03.021) on Windows, November 2026, SQLite through the SLE driver (SQLite 3.50.2).

SQLite does the calendar

SQLite's date() takes modifiers that cover everything except the parsing:

SELECT date('now', 'localtime', '+7 days');
SELECT date('now', 'localtime', '+1 days', 'weekday 1');
Enter fullscreen mode Exit fullscreen mode

weekday N moves forward to the next day with that weekday number (0 = Sunday), or stays if it already is one. Starting from tomorrow (+1 days) makes "mon" on a Monday mean next week's Monday.

The operation

The service operation turns the input into one modifier string and asks SQLite for the date, then builds the label in a second query. PARSE_DAY is the app's existing date parser for typed dates; it rejects impossible dates such as 30.02.

public operation DAY_INFO
params
    string pText : IN
    string pIso : OUT
    string pLabel : OUT
    string pError : OUT
endparams
variables
    string vT, vNum, vMod, vOk
    numeric vPos, vLen
endvariables
    pIso = ""
    pLabel = ""
    pError = ""
    vT = $lowercase(pText)
    call TRIM_TEXT(vT)
    if (vT = "")
        return 0
    endif
    vMod = ""
    if (vT = "today" | vT = "t")
        vMod = "+0 days"
    endif
    if (vT = "tomorrow" | vT = "tm")
        vMod = "+1 days"
    endif
    if (vMod = "" & vT[1:1] = "+")
        vNum = vT[2:10]
        vPos = $scan(vNum, "w")
        if (vPos > 0)
            vLen = vPos - 1
            vNum = vNum[1:vLen]
        endif
        call SQL_ESCAPE(vNum)
        call GET_TEXT("SELECT CASE WHEN '%%(vNum)' GLOB '[0-9]*' AND NOT '%%(vNum)' GLOB '*[^0-9]*' AND CAST('%%(vNum)' AS INTEGER) <= 3650 THEN 'Y' ELSE 'N' END AS OK", vOk)
        if (vOk != "Y")
            pError = "Use +N for N days or +Nw for N weeks, for example +7 or +2w."
            return -1
        endif
        if (vPos > 0)
            call GET_TEXT("SELECT '+' || (CAST('%%(vNum)' AS INTEGER) * 7) || ' days' AS M", vMod)
        else
            vMod = "+%%(vNum) days"
        endif
    endif
    if (vMod = "")
        vPos = $scan(",sun,mon,tue,wed,thu,fri,sat,", ",%%(vT),")
        if (vPos > 0 & $length(vT) = 3)
            vPos = (vPos - 1) / 4
            vMod = "+1 days', 'weekday %%(vPos)"
        endif
    endif
    if (vMod != "")
        call GET_TEXT("SELECT date('now', 'localtime', '%%(vMod)') AS D", pIso)
    else
        call PARSE_DAY(pText, pIso, pError)
        if ($status < 0)
            return -1
        endif
    endif
    call GET_TEXT("SELECT substr('SunMonTueWedThuFriSat', CAST(strftime('%w', d) AS INTEGER) * 3 + 1, 3) || ', ' || strftime('%d.%m.%Y', d) || ' (' || CASE WHEN n = 0 THEN 'today' WHEN n = 1 THEN 'tomorrow' WHEN n = -1 THEN 'yesterday' WHEN n > 1 THEN 'in ' || n || ' days' ELSE (0 - n) || ' days ago' END || CASE WHEN strftime('%w', d) IN ('0', '6') THEN ', weekend' ELSE '' END || COALESCE((SELECT ', ' || h.HOLIDAY_NAME FROM APP_HOLIDAY h WHERE h.HOLIDAY_DATE = d), '') || ')' AS LBL FROM (SELECT '%%(pIso)' AS d, CAST(julianday('%%(pIso)') - julianday(date('now', 'localtime')) AS INTEGER) AS n)", pLabel)
    return 0
end
Enter fullscreen mode Exit fullscreen mode

Notes:

  • +Nw is turned into days by SQLite as well (CAST(... AS INTEGER) * 7), so the value is checked to be digits only and at most 3650 before it is used.
  • For weekdays, $scan finds the position of ,mon, in ,sun,mon,tue,...; (position - 1) / 4 is the weekday number.
  • The weekday modifier is inserted as +1 days', 'weekday 1 inside the quotes, which yields two modifiers in the final statement.
  • The label query starts from a one-row subselect with the date d and the distance in days n, so the CASE expressions stay readable. The whole expression has an alias (AS LBL); with this driver, long unnamed result columns caused trouble earlier in the project.

In the form

The due date field calls the operation in its loseFocus trigger, writes the ISO date back into the field and shows the label next to it. Buttons for Today, +1 day, +1 week and Next Monday just put the shortcut into the field and call the same entry, so there is one code path.

Tests

The automatic test of the module checks every shortcut against SQLite directly, for example that +2w equals date('now', 'localtime', '+14 days'), that mon equals the weekday 1 expression above, that 31.12.2026 is labelled Thu, 31.12.2026, that 30.02.2026 and +x are rejected, and that a test holiday three days ahead shows up in the label. Because the expected values are computed by SQLite at test time, the test does not depend on the day it runs.

Top comments (0)