Shape · Dates and time

Convert Date/Time to Text

Turns a date into text using a pattern, such as 01/03/2024 or Mar 2024. The result is text, so use it for labels and exports, not for date math.

Also known as: Date format, strftime

How it works

Each format row names a column, a pattern and an optional output column. Pick a pattern from the presets or type your own with the tokens YYYY, YY, MM, DD, HH, hh, mm, ss, MMM, MMMM and A (AM/PM); a raw strftime pattern such as %d/%m/%Y also works. Leave the output blank to overwrite the column in place, which turns it into text, or name a new column to keep the date. A text column holding ISO dates is accepted too. Rows with no column or no pattern are ignored. If you only want dates to print differently in a report, use Format Table instead.

Inputs and outputs

Settings

formats: [{ column: string, pattern: string, outputColumn?: string }]  // pattern = a strftime format (DuckDB strftime), e.g. "%d/%m/%Y" gives 01/03/2024 and "%b %Y" gives Mar 2024; the result is text

Example

You want a month label for a chart while keeping order_date for sorting. Add a format: column order_date, pattern MMM YYYY, output month_label.

More in Dates and time

• Extract Date/Time Parts — Pulls parts out of a date into new columns: year, quarter, month, week, day, weekday, hour, a weekend flag and more.
• Date/Time Arithmetic — Adds or subtracts time from a date, or measures the gap between two dates, from seconds to years or working days. For example, due date = order date + 30 days.
• Truncate Date/Time — Rounds dates down to the start of a period, such as the first of the month. Handy before grouping by month.
• Time of Day — Drops the date from a timestamp and keeps the clock time, so you can compare times across days.
• Make Date from Parts — Combines separate year, month and day columns (and hour, minute, second) into one real date or timestamp.
• Convert Serial Date — Converts between numbers and dates: Unix timestamps, Excel serial dates, Julian days or your own starting point. For example, 45292 from Excel becomes 2024-01-01.
• Convert Timezone — Shifts times from one time zone to another, with daylight saving handled. For example, UTC to Europe/Warsaw.