Date & Time Formats

Display Formats

Three settings decide how dates are written throughout the application.

Where to find it

Architect Panel → Configuration:

• Site Settings — the option groups, grouped by subject

The three

• Date — day, month and year. The default is day first.
• Date and time — the above with hours and minutes.
• Date and time with seconds — for anything where precision matters, such as logs.

They use the standard formatting characters, so any arrangement is possible.

Numeric dates are genuinely ambiguous

The central problem. 03/04/2026 is the third of April to a British reader and the fourth of March to an American one, and nothing in the string says which.

If your users are all in one country, the local convention is fine. If they are not, or if the date will be read outside your organisation, use a format with a named month — it cannot be misread.

Prefer four-digit years

Always. Two-digit years save two characters and introduce a genuine ambiguity, and there is no situation where they are worth it.

Choose one clock and keep it

Twelve-hour with a meridiem, or twenty-four hour. Both are fine; mixing them within an application is not, and it happens when the three formats are set at different times by different people.

Seconds where they earn their place

Logs, audit trails, anything where ordering within a minute matters. Not on a due date, where they are noise.

Consistency is worth more than elegance

A single readable format used everywhere beats a clever arrangement used in some places and not others. Users learn one format quickly and misread a second one indefinitely.

They must match the conversion formats

The separate database-side conversion formats have to describe the same arrangement. A display format saying day-first and a conversion format saying month-first will disagree for the first twelve days of every month and agree thereafter — which is a bug that appears and disappears.

Set them at installation

Changing formats later is safe — no data changes — but it does change every screen and every export at once. Better to settle it early.

Worked example

An organisation with users in three countries chose a named-month format for display and matched its conversion formats to it. A test on the fifth of a month confirmed both agreed, which had previously masked a mismatch that only showed after the twelfth.

Recommendations

• Named months where readers are international.
• Four-digit years, always.
• One clock convention across all three formats.
• Match the conversion formats and test after the twelfth.

Conversion Formats

Two further settings describe the same date formats in the database’s own syntax, for conversion within queries.

Where to find it

Architect Panel → Configuration:

• Site Settings — the option groups, grouped by subject

Architect Panel → Data:

• Query Builder — where a conversion format is used

Why they exist

Some work happens in the database rather than in the application — sorting, grouping, filtering and comparing dates inside a query. The database needs the format described in its own syntax to do that.

Hence a second pair of settings saying the same thing in a different language.

They must describe the same arrangement

The single most important point. If the display format is day-first and the conversion format is month-first, they agree for the thirteenth onward and disagree for the first twelve days of every month.

That is a bug which appears for twelve days, vanishes for the rest of the month, and is almost impossible to reproduce on demand.

The syntax differs

The database uses percent-prefixed codes rather than the application’s single letters. They are not interchangeable, and pasting one into the other produces a literal string rather than a date.

Change them together

Any time you change a display format, change the matching conversion format in the same sitting. Half a change is worse than none, because the two now positively disagree.

Test after the twelfth

Or better, test on a date where day and month cannot be confused — the twenty-fifth, say. A mismatch that would be invisible on the third is obvious on the twenty-fifth, because one side simply fails.

Watch it after upgrades

Database format codes are stable but not immune to change, and a query relying on one is worth checking after a major database upgrade.

Prefer letting the platform convert

Where you have the choice. Handling dates as dates and formatting them at the point of display avoids the whole class of problem. Conversion inside a query is for when you genuinely need the database to do the work.

Time zones apply here too

A conversion in a query uses the database’s idea of time, which may not be the application’s. For anything where an hour matters, confirm which zone a query is working in rather than assuming.

Worked example

A monthly report was correct except during the first fortnight, when a handful of records fell into the wrong month. The display format was day-first and the conversion format month-first. Correcting the conversion format fixed it, and both are now changed together.

Recommendations

• Both formats must describe the same arrangement.
• Change them in the same sitting.
• Test on a day past the twelfth.
• Let the platform format where you can.