AML Date Format

Note

Date Format applies only to fields with type: "date". Datetime fields have no data format. To format a datetime value, set the field's type to date, then use the date and time tokens below, for example dd LLL yyyy HH:mm.

Introduction

To format your date fields, add a format property with a string pattern representing your chosen format.

Model orders {
  dimension order_created_at_raw {
    label: "Order Created At Raw"
    type: "date"
    definition: @sql {{ #SOURCE.created_at }};;
    format: "dd-LL-yyyy"
  }

  dimension order_created_at {
    label: "Order Created At"
    type: "date"
    definition: @sql {{ #SOURCE.created_at }};;
    format: "dd LLL, yyyy"
  }
}

Syntax

Modeling fields format

Fields in models and datasets take format: String.

At creation, a date field shows as LLL dd yyyy (e.g., Dec 31 2020). Users can define the format once here, and every viz referencing the field inherits it by default.

dimension order_created_at {
  type: "date"
  format: "dd LLL, yyyy"
}

Viz fields format

Fields inside a chart take format: FieldFormat.

By default, a viz field inherits the modeling format. Users can override the format for a specific field inside each viz.

VizFieldFull {
  ref: r(orders.created_at)
  format {
    type: "date"
    pattern: "dd LLL, yyyy"
  }
}

The pattern is the same in both. Only the wrapper differs.

Pattern

A date format pattern is a sequence of tokens and separators. If a pattern uses anything outside the supported tokens and separators, Holistics falls back to LLL dd yyyy (e.g., Dec 31 2020).

Date

Use these tokens for the day, month, and year parts of a date.

Component	Token	Example
Day of month	d, dd	1, 01
Month	L, LL, LLL, LLLL M, MM, MMM, MMMM	1, 01, Jan, January
Year	yy, yyyy	26, 2026
Day of week	EEE, EEEE	Mon, Monday

M and L both represent the month (e.g., MMM and LLL display Jan), but M shows the month name in the viewer's language with embedded localization.

Time

Use these tokens for the time part of a timestamp.

Component	Token	Example
Hour	H, HH (24-hour) h, hh (12-hour)	0–23, 00–23 1–12, 01–12
Minute	m, mm	0–59, 00–59
Second	s, ss	0–59, 00–59
AM/PM	a	AM, PM

When exporting to Excel, Holistics converts these formats:

• Minute vs. month: Excel reads m/mm as minutes only right after an hour (H, HH, h, hh) or right before a second (s, ss), like HH:mm. Anywhere else (e.g., mm/dd/yyyy), it reads a month, so Holistics exports the value as text.
• Square brackets: Excel reads [HH]:mm as elapsed time, so Holistics exports the brackets as plain characters.

Range

Use these tokens to show a period, such as a quarter or a week.

Component	Token	Example
Quarter	Qq	Q1
Week range	wwww	Feb 24 - Mar 01, 2020
Week number	W, WW	1, 01

When exporting to Excel, formats that contain a range token are exported as text.

Separators

Type	Token	Description
Separator	. / - , : and a space	Goes between tokens
Wrap	() [] {}	Wraps a single token
Free text	' '	Quote to add literal text, e.g., dd 'of' LLLL yyyy

Examples

Here is how different patterns display the same raw value, 2023-12-31 14:05:09.

Format pattern	Result
dd/LL/yyyy	31/12/2023
dd-LL-yyyy	31-12-2023
LL/dd/yyyy	12/31/2023
LLL dd yyyy	Dec 31 2023
LLL dd, yyyy	Dec 31, 2023
dd LLL, yyyy	31 Dec, 2023
LLL yyyy	Dec 2023
yyyy	2023
EEE, dd LLL yyyy	Sun, 31 Dec 2023
yyyy Qq	2023 Q4
'W'WW yyyy	W52 2023
wwww	Dec 25 - 31, 2023
HH:mm	14:05
HH:mm:ss	14:05:09
h:mm:ss	2:05:09
h:mm a	2:05 PM
[HH]:mm	[14]:05
yyyy-LL-dd HH:mm:ss	2023-12-31 14:05:09