Date/Time Issue between SQL and Power BI Desktop

[eliassal]

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

[simrantuli]

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

[eliassal]

Power query, where in Power BI? I am confused, I thought that Power query is used in excel !!!

[v-yingjl]

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 Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[eliassal]

Li, you are showing exactly the same thing, your text in the horizantal bar is showing date and time on each bar as mine which is my problem. This is exactly what I do, in the preview in powerbi it always shows date time I am not sure you have rad my initial post. I want to get rid of the zone time phrase, just want to see the date on the bar

So I need iot to be

instead of

[eliassal]

Li, you are showing the same thing which I want to get rid of the time in the 1st column

The initial question, why Power BI adding time by iteself?

[v-yingjl · Accepted solution]

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 Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[eliassal]

The owner is MDS corporation 😂 thanks for the tips, I have already started to use the stacked bar chart but the other visual is much nicer for visualization

[v-yingjl]

Hi @eliassal ,

If the above posts help, please kindly mark it as a solution to help others find it more quickly. Thanks!

Best Regards,
Yingjie Li

[BA_Pete]

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

[amitchandak]

@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

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

[eliassal]

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