Skip to content
Version 2026.25

Report text: dates, numbers and query parameters ​

Text in a report band (a label, a text field, a binding on any property) can format numbers and dates, and a report's parameters reach its SQL queries. This page says what each form does and which time zone a date lands in.

Dates ​

format(value, pattern) and dateFormat(value, pattern) print a date or time. The pattern language is the same one the dateFormat function uses in a screen's expression transform, so a pattern means the same on a screen and in a report.

LettersMeaningExample
yyyy, yyYear2026, 26
MM, MMM, MMMMMonth number, short name, full name10, Oct, October
ddDay of month28
EEE, EEEEWeekday, short and fullWed, Wednesday
HH, hhHour on a 24-hour and a 12-hour clock14, 02
mm, ss, SSSMinutes, seconds, milliseconds30, 05, 250
aAM or PMPM
'text'Text printed as written'at'
=format(row("sampledAt"), "yyyy-MM-dd HH:mm")
=dateFormat(param("start"), "EEEE, MMMM d, yyyy")
=format(row("sampledAt"), "HH:mm", "America/Chicago")

row("field") is the current row's field inside a repeating band, the same value {row.field} gives, in a form that can be passed to format.

The value is either ISO 8601 text (2026-10-28, 2026-10-28T14:30:00, 2026-10-28T14:30:00Z, 2026-10-28T14:30:00+02:00) or, for dateFormat, a number of milliseconds since 1 January 1970 UTC. Anything else comes back unchanged. A number given to format with a number pattern (f:2, %.2f) is still formatted as a number.

Time zone ​

  • A value with a zone (Z or an offset such as +02:00, or a number of milliseconds) is an exact moment. It is shown in the gateway machine's own time zone. Name another zone as the last argument, as an IANA name such as America/Chicago or UTC, to show it there instead. UTC is never used unless you ask for it.
  • A value without a zone (2026-10-28, 2026-10-28T14:30:00, or 2026-10-28 14:30:00 as SQLite's datetime() returns it) is a wall-clock time and is printed as written, with no conversion. A date alone never moves to the day before or after. If a query stores UTC without a zone and you want it shown in local time, return it with a Z (strftime('%Y-%m-%dT%H:%M:%SZ', t)). The tagHistory source already returns Z timestamps.

now() and today() return the gateway's local time without a zone, so they print as they are.

Query parameters ​

A report's parameters bind to the :name placeholders in its SQL, the same way a named query's parameters do. The value is always sent to the database as a bound value and never pasted into the SQL text.

  • Query data source. Write :name for each report parameter you want, for example SELECT * FROM readings WHERE day >= :start AND day < :end. Every :name in the SQL must be a report parameter that has a value, or the report refuses to run and says which one is missing.
  • Named query data source. A report parameter with the same name as one of the named query's parameters replaces that parameter's default. A query parameter the report does not have keeps its own default. The query must still declare every parameter its SQL uses.
  • Parameter values are converted to the declared type first, so a number parameter binds as a number. Dynamic values in a schedule ({today}, {weekStart}, {monthStart}) are resolved before they are bound, and a scheduled run binds them the same way a run from a screen does.

A named query runs on the database connection it is configured with. A report no longer has to use the default connection.