DateTime.ToText

Updated on
Rick de Groot

DateTime.ToText is a Power Query M function that is designed to convert datetime values into their textual representations. It does this based on the format and culture options you provide. The result is a text value that mirrors the datetime in the format you’ve specified.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

DateTime.ToText(
   dateTime as nullable datetime,
   optional options as any,
   optional culture as nullable text,
) as nullable text
ArgumentAttributeDescription
DateTimeThe datetime value to transform into text.
OptionsOptionalThis is an optional argument where you can provide a record parameter with additional instructions. The Format field is used to specify the output format of the transformation. You can also provide a Culture field to ensure the transformation supports local formats. For example: [ Format = “dd MMM yyyy HH:mm:ss”, Culture = “nl-NL” ]. If you omit this argument, the default format used will align with the culture of your system, using Culture.Current.
CultureOptionalThis is an optional argument used for legacy workflows only. You can specify the culture as a text value here.

Description

The function returns a textual representation of a dateTime value. You can provide an optional record parameter, options, to specify additional properties. The culture argument is only used for legacy workflows.

Examples

Let’s delve into some examples of how to use the DateTime.ToText function:

Basic conversion

Consider converting the datetime value #datetime( 2010, 12, 31, 01, 30, 25 ) into a text value. The output may differ based on the current culture.

// Output: "12/31/2010 1:30:25 AM"
DateTime.ToText( #datetime( 2010, 12, 31, 01, 30, 25 ) )

Custom Date Formats

The DateTime.ToTextfunction allows you to format date and time using a wide range of Custom Date and Time Format Strings. For example, to format a date-time value into the format “dd MMM yyyy HH:mm:ss.ffffff”, you can use the following code:

// Output: "30 Dec 2010 02:04:50.369730"
DateTime.ToText(
  #datetime( 2010, 12, 30, 2, 4, 50.36973 ), 
  [Format="dd MMM yyyy HH:mm:ss.ffffff"] 
)

Below is a table showing the format strings unique to DateTime values. The table shows how each format displays the date and time:

FormatDescriptionDecember 31, 2023 1:30:25December 29, 2023 22:04:50.36973183
gGeneral date short time12/31/2023 1:30 AM12/29/2023 10:04 PM
GGeneral date long time12/31/2023 1:30:25 AM12/29/2023 10:04:50 PM
sShort time format2023-12-31T01:30:252023-12-29T22:04:50
uUniversal full format2023-12-31 01:30:25Z2023-12-29 22:04:50Z
fFull date short timeSunday, December 31, 2023 1:30 AMFriday, December 29, 2023 10:04 PM
FFull date long timeSunday, December 31, 2023 1:30:25 AMFriday, December 29, 2023 10:04:50 PM

You’re not limited to just these options. Any Custom format strings for date values or time values can also be used with this function. For more examples on formatting just the date part you can check out the Date.ToText function function, and for time-related format strings, look at the Time.ToText function function.

To learn the possibilities and see what other format strings are there, you can have a look at this Overview of all Custom Format Strings.

Culture Code

The DateTime.ToText function also supports a culture code in the ‘options’ argument. Suppose you want to convert a DateTime value into text using the ISO 8601 pattern.

// Output: "2000-02-08T03:45:12Z"
DateTime.ToText( 
  #datetime( 2000, 2, 8, 3, 45, 12 ),
  [Format="yyyy-MM-dd'T'HH:mm:ss'Z'", Culture="en-US"] 
)

The culture code can significantly influence the result of the DateTime.ToText function. For instance, not all countries use the AM/PM notation, and month names and date formats can vary greatly between countries.

The image below illustrates the differences between the Dutch (nl-NL) and American (en-US) culture codes. Notice the difference in date format (mm/dd/yyyy vs dd-mm-yyyy)? And how the Netherlands doesn’t use AM/PM?

DateTime.ToText-function-using-a-Custom-date-Format-and-Culture-in-Power-Query-M

Learn more about DateTime.ToText in the following articles:

Other functions related to DateTime.ToText are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/datetime-totext

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy