Forum Discussion
Date/Time Issue between SQL and Power BI Desktop
Hi, I have a select statement that runs fine in SSMS it returns 1st column as date as follows
SELECT
CAST(timestamp AS DATE) AS daily,
Datepart(Year, Timestamp) as Year,
Datepart(Month, Timestamp)-1 as Month,
Datepart(Day, Timestamp) as Day,
COUNT(ipaddress) AS ipperday............
.........
The outcome is
2020-02-17 2020 1 17 506
2020-02-18 2020 1 18 2
2020-02-19 2020 1 19 539
Whereas when, in transformation in PowerBI desktop, New query, I execute the same exact query, 1st colmn comes back with Date and time as follows
3/28/2020 12:00:00 AM 2020 3 28 1
8/3/2020 12:00:00 AM 2020 8 3 631
5/15/2020 12:00:00 AM 2020 5 15 363
2/23/2020 12:00:00 AM 2020 2 23 1128
I use the Horizantal Bar chart from MS and it duispalys an ugly text on the bars a follows
When I change the type in Power BI to date, my report does not get execy=uted because it is a direct query
So how can I overcome this and have the exact same result in Power BI without time? Thanks
Hi eliassal ,
Becasue date type in sql server is corresponded with date/time type in power bi desktop.
If you want to use horizontal bar chart to show the date without time, I think you should contact the owner of this custom visual because this custom visual shows time automatically and we could not change it settings.
One temporary way to use this visual to show date without time is to change the data type in cast() function from date to varchar:
Or you can use default bar charts in power bi such as Stacked bar chart, change the format of daily field and change the type of Y-axis as Categorical:
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
11 Replies
eliassal , how you have done these transformations.
There are quite a few stuff is not supported as a custom column in Direct query
Check-in dax as a calculated column.
Format example you can take from
It is a straight forward simple select
SELECT
CAST(timestamp AS DATE) AS daily,
Datepart(Year, Timestamp) as Year,
Datepart(Month, Timestamp)-1 as Month,
Datepart(Day, Timestamp) as Day,
COUNT(ipaddress) AS ipperday
FROM mytable
WHERE timestamp< GETDATE()
GROUP BY
CAST(timestamp AS DATE),
Datepart(Year, Timestamp),
Datepart(Month, Timestamp)-1,
Datepart(Day, Timestamp)
ORDER BY daily ASC
Hi eliassal ,
You could try a different method of truncating your datetime to date via the SQL query.
Check out this thread for some good suggestions:
https://stackoverflow.com/questions/923295/how-can-i-truncate-a-datetime-in-sql-server/923322
Pete
Hi eliassal ,
How are you doing this transformation?
I recreated your scenario and changed the timestamp to Date in Power query and it worked fine for me. Didn't give me any errors.
However, if it still doesn't work for you, you could change the format of this column in 'Model' view. Please find below the screenshot.
Regards
Simran Tuli
Power query, where in Power BI? I am confused, I thought that Power query is used in excel !!!
Hi eliassal ,
Based on my test, I can get the same result using the sql statement in power bi and need not change the data type of daily column as date type.
You can open power query editors by choosing transforming data.
You can try to paste your sql statement in the advanced options when connecting to sql server to check. Please note that ORDERBY cannot be quoted under direcr query mode.
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
