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.