Date/Time Formats

Date and Date/Time Display Formats

Dates appear everywhere — record rows, reports, exports, emails. Different locales have different conventions; choosing the right format matters for clarity and avoiding ambiguity (08/09/2025 means very different things in the US vs UK).

Format Categories

• Short date: Compact for table cells, e.g. 13/05/2026.
• Long date: Friendly for content, e.g. 13 May 2026.
• Short datetime: 13/05/2026 14:32.
• Long datetime: 13 May 2026, 2:32 pm.
• Relative: “2 hours ago”, “Yesterday”, “Last week”.
• ISO: 2026-05-13 — universally unambiguous, computer-friendly.

Recommended by Locale

• UK / Europe: DD/MM/YYYY (or D MMM YYYY for long).
• US: MM/DD/YYYY.
• ISO standard: YYYY-MM-DD (best for technical / audit views).
• East Asia: YYYY-MM-DD or YYYY年MM月DD日.

Where the Format Is Applied

• Table column cells.
• Form input displays (the input parser still accepts other formats).
• Report headers and labels.
• Email templates (where tokens use the format helper).
• Document templates (PDF outputs).
• Exports (CSV, Excel — though always-recommended to use ISO for exports).

Worked Examples

• UK SaaS: Short DD/MM/YYYY, long D MMM YYYY, datetime DD/MM/YYYY HH:mm.
• US enterprise: Short MM/DD/YYYY, long MMM D YYYY, datetime MM/DD/YYYY h:mm a.
• International audit logs: Force ISO (YYYY-MM-DD HH:mm:ss) regardless of user locale — avoids ambiguity in compliance reviews.
• Multi-tenant SaaS: Each tenant sets their own default; users can override.

MySQL-side Date Conversion Formats

When data needs to be filtered or sorted by date on the database side, MySQL has its own date-format syntax — different from PHP's. Knowing the conversion is essential when writing custom queries, reports or query-builder formulas that operate on dates.

MySQL Format Specifiers

• %Y — 4-digit year (2026).
• %y — 2-digit year (26).
• %m — month (01-12).
• %c — month (1-12) without leading zero.
• %d — day of month (01-31).
• %e — day (1-31) without leading zero.
• %H — hour (00-23).
• %h / %I — hour (01-12).
• %i — minute (00-59).
• %s — second.
• %p — AM/PM.
• %W — weekday name (Monday).
• %a — abbreviated weekday (Mon).
• %M — month name (January).
• %b — abbreviated month (Jan).

Common Patterns

• DATE_FORMAT(created_at, '%Y-%m-%d') — ISO date.
• DATE_FORMAT(created_at, '%d/%m/%Y') — UK short date.
• DATE_FORMAT(created_at, '%d %M %Y') — UK long date.
• DATE_FORMAT(created_at, '%Y-%m') — year-month for grouping.
• DATE_FORMAT(created_at, '%Y-W%u') — ISO week.

Where You'll Use This

• Custom report SQL.
• Query Builder advanced filters.
• Database views.
• Bulk update scripts.
• Migration / backfill scripts.

Worked Examples

• Monthly revenue report: SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(total) FROM orders GROUP BY month.
• Weekly cohort analysis: Group by DATE_FORMAT(signup_date, '%Y-W%u').
• Day-of-week breakdown: DATE_FORMAT(created_at, '%W') for grouping by Monday, Tuesday, etc.
• Date range conversion: STR_TO_DATE('13/05/2026', '%d/%m/%Y') to parse a UK-formatted string into a DATE.