Forum Discussion
Need help converting datetime to text
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?
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!
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?
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!