Date.ToText
Writes a date as text, in the culture's short form or in a format you give.
Group: Dates and times. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Date.ToText(date as nullable date, optional options as any, optional culture as nullable text) as nullable text| Parameter | Type | Description |
|---|---|---|
date | nullable date | The date to write; null gives null. |
options | any optional | A record [Format = ..., Culture = ...], both fields optional; or, as older queries do, a format as text. |
culture | nullable text optional | A culture's name, when options is a format given as text. |
Returns
nullable text. The date as text.
Remarks
- Text follows the culture: the one given, else the query's (
Culture.Current), which is the model's Locale for import, else the model's own culture, else en-US. These examples run in en-US unless a culture is given. Left without a format, a date is written in the culture's short form: 3/15/2024 in en-US. - A format is built from letters:
yyyythe year,MMthe month as two digits,MMMandMMMMits short and full name,ddthe day,dddandddddthe weekday's short and full name,HHthe hour on a 24-hour clock,hhon a 12-hour clock withttfor AM or PM,mmminutes,ssseconds. Text in single quotes is copied as it is. - Single letters name a culture's standard forms: "d" the short date, "D" the long one. In de-DE they give 15.03.2024 and Freitag, 15. März 2024.
- A format in the options record must be a date's: one with hours or minutes in it is an error.
Compared with Power Query
Known difference in OpenBI Studio 0.3.79, found while this page was written and run on its engine:
Date.ToText(#date(2024, 3, 5), [Culture = "fa-IR"]): Power Query gives "15/12/1402", the date in the Persian calendar of the fa-IR culture (ar-SA's Hijri calendar gives "24/08/45"). OpenBI writes and reads dates in the Gregorian calendar and the Thai Buddhist one, and raises an error that says so for these two.
Examples
The culture's short date
Date.ToText(#date(2024, 3, 15))"3/15/2024"A sortable date for a file name
Date.ToText(#date(2024, 3, 15), [Format = "yyyy-MM-dd"])"2024-03-15"A Norwegian heading
Date.ToText(#date(2024, 3, 15), [Format = "dddd d. MMMM yyyy", Culture = "nb-NO"])"fredag 15. mars 2024"