- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Need help converting datetime to text
I am using DateTime.ToText (in the M language) in a custom column to convert a datetime to text and concatenate it to the value in another column, and I am having trouble getting it into a format that is useful for my report. The M language reference for the function says to check the Library Specification, but the latest version of this document I can find (Feb. 2015) only gives a few format elements - none of which can apparently handle a 24-hour format for the hour portion. Testing it out, the "hh" portion is always 01-12, with 12:00 being either midnight or noon and 01 being either 1:00 AM or 1:00 PM.
The problem is that I need to compare this to a text column generated by SQL Server, which has only a few, fixed formatting options as well. The most appropriate ones for my report use a 24-hour clock. So I am trying to create soemthing like "yyyy-mm-dd hh:mi:ss" (the "ODBC canonical" format in SQL Server), where "hh" would be 13 for 1:00pm. Even the format "yyyy-mm-ddThh:mi:ss.mmm", which appears to match the ISO8601 format in SQL Server, only uses a 12-hour clock.
Is there a simple way to do this without having to parse it into its components and re-assemble it from individual pieces of text?
Thanks.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I found the solution, so I'm posting it here in hopes it may help someone else.
Using "yyyy-mm-dd HH:mm:ss" did the trick - capitalizing the HH create a 24-hour clock, from 00-23. So my formula reads DateTime.ToText([KeyDateTime],"yyyy-mm-dd HH:mm:ss") & " " & [UnitId] and this compares correctly to the SQL Server output. I can even create a relationship using this column.
It's odd that this is not documented anywhere I could find it, in this forum or the MSDN doc or even with a Google search. And I've run into this with other functions as well, where the documentation refers to elements but there is no specification for those elements. I respect the fact that Power BI Desktop is growing quickly, and I love that, but surely such simple parts of the language such as format strings must be documented somewhere?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Actually, the formula should read "DateTime.ToText([KeyDateTime],"yyyy-MM-dd HH:mm:ss") & " " & [UnitId]" - who would have guessed that "MM" means month and "mm" means minute? It really should be documented somewhere!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I found the solution, so I'm posting it here in hopes it may help someone else.
Using "yyyy-mm-dd HH:mm:ss" did the trick - capitalizing the HH create a 24-hour clock, from 00-23. So my formula reads DateTime.ToText([KeyDateTime],"yyyy-mm-dd HH:mm:ss") & " " & [UnitId] and this compares correctly to the SQL Server output. I can even create a relationship using this column.
It's odd that this is not documented anywhere I could find it, in this forum or the MSDN doc or even with a Google search. And I've run into this with other functions as well, where the documentation refers to elements but there is no specification for those elements. I respect the fact that Power BI Desktop is growing quickly, and I love that, but surely such simple parts of the language such as format strings must be documented somewhere?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Actually, the formula should read "DateTime.ToText([KeyDateTime],"yyyy-MM-dd HH:mm:ss") & " " & [UnitId]" - who would have guessed that "MM" means month and "mm" means minute? It really should be documented somewhere!
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
01-31-2023 01:08 AM | |||
12-04-2024 12:48 AM | |||
10-23-2024 09:39 AM | |||
10-06-2024 08:56 PM | |||
10-21-2024 11:50 AM |
User | Count |
---|---|
126 | |
81 | |
59 | |
57 | |
43 |
User | Count |
---|---|
183 | |
111 | |
82 | |
65 | |
50 |