Dates often arrive as text: 15/05/2026 from a CSV, 2026-05-15 08:30:00 from a log. QuickSight
needs a real date to sort, filter by range and use the date functions. There are two ways to get one:
change the field type in the dataset, or write a parseDate calculated field.
Copy and paste
Day/month/year text:
parseDate({Order Date Text}, 'dd/MM/yyyy')
ISO text (the default format, so the pattern can be left out):
parseDate({Order Date Text})
Text with a time:
parseDate({Created Text}, 'yyyy-MM-dd HH:mm:ss')
Compact text such as 20260515:
parseDate(concat(left({Day Key}, 4), '-', substring({Day Key}, 5, 2), '-', right({Day Key}, 2)))
If the compact value is a number, wrap it in toString({Day Key}) first.
The pattern
The pattern is case-sensitive and uses the Joda letters: yyyy year, MM month number, MMM month
abbreviation (May), dd day, HH hour (0 to 23), mm minutes, ss seconds. MM and mm are the
classic mix-up: dd/mm/yyyy reads the middle part as minutes.
Which patterns work depends on where the query runs. File uploads, S3 and Athena accept any Joda
pattern; SPICE, Redshift, PostgreSQL and the other databases accept a fixed list, which covers the
usual combinations of dd, MM, MMM and yyyy with / or -, with or without HH:mm:ss. The
compact yyyyMMdd without a time is not on the list, which is why the formula above rebuilds it as
yyyy-MM-dd.
Rows that do not match
The documentation says that parseDate skips rows that don’t conform to the format, including nulls.
A column with mixed formats, or a placeholder such as unknown, can therefore lose rows without an
error. Before you build on the new field, compare the row count of a table visual with and without it,
or count the rows whose text is not empty.
Or change the type in the dataset
In the dataset editor, click the data type under the column name, choose Date, and enter the pattern in Edit date format. Validate shows whether the rows parse. Rows that don’t validate are skipped from the dataset, so the same check applies. AWS notes that the text-to-date change is supported for relational (SQL) sources.
Converting in the dataset is the better default: every analysis gets a proper date field, and nobody
has to remember the pattern. Use parseDate when the text needs work first, like the compact key
above, or when you can’t edit the dataset.
Time zones
The result is a date-time without a time zone, which QuickSight treats as UTC. If the text holds local times and the analysis uses a custom time zone, QuickSight shifts the values for display; see how to convert a Unix timestamp to a date for the same question with epoch values.
Checked against the Amazon Quick Sight documentation (October 2026).
