Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I was trying to change the datetime format to date in the SQL statement in order to lessen the M query due to connecting to a huge database.
Here is my code: convert(date, createTime, 101), but turned out error. What is the appropriate command for it?
Solved! Go to Solution.
The createtime column is in datetime format.
Only the cast(createtime as date) works for me. The result is in the format of datetime too.
Is that possible to direct convert it to date format without using query M?
What is the datatype for your createTime column in SQL? Is it a Date-Time column or a character based data type (Char/NChar?)
you could try the following
CAST(createTime AS Date)
or
convert(date, createTime, 103)
or
TRY_CAST(createTime AS Date)
The createtime column is in datetime format.
Only the cast(createtime as date) works for me. The result is in the format of datetime too.
Is that possible to direct convert it to date format without using query M?
Date format is DateTime only set to midnight.
That's obvious and is clearly not the question.
You are aware that in M there's a data type Date that removes the Time component completely? So why when in SQL you explicitly CAST DateTime as DATE PowerQuery is not picking it up as Date data type but instead is treating it as DateTime with 00:00:00 in the time part? It looks ugly and is confusing.
Any updates on this or do I have to explicitly FIX every date column in Power BI? It's a complete pain when you've already specified it as a DATE data type in Microsoft SQL Server.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |