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
DateTime.ToText( dateTime as nullable datetime, optional options as any, optional culture as nullable text, ) as nullable text
|DateTime||The datetime value to transform into text.|
|Options||Optional||This is an optional argument where you can provide a record parameter with additional instructions. The |
|Culture||Optional||This is an optional argument used for legacy workflows only. You can specify the culture as a text value here.|
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.
Let’s delve into some examples of how to use the DateTime.ToText function:
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:
|Format||Description||December 31, 2023 1:30:25||December 29, 2023 22:04:50.36973183|
|g||General date short time||12/31/2023 1:30 AM||12/29/2023 10:04 PM|
|G||General date long time||12/31/2023 1:30:25 AM||12/29/2023 10:04:50 PM|
|s||Short time format||2023-12-31T01:30:25||2023-12-29T22:04:50|
|u||Universal full format||2023-12-31 01:30:25Z||2023-12-29 22:04:50Z|
|f||Full date short time||Sunday, December 31, 2023 1:30 AM||Friday, December 29, 2023 10:04 PM|
|F||Full date long time||Sunday, December 31, 2023 1:30:25 AM||Friday, 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.
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?
Learn more about DateTime.ToText in the following articles:
- Mastering Custom Format Strings in Power Query
This guide shows how to use custom format strings to format values in the M language. » Read more
Other functions related to DateTime.ToText are: