SQL Server DQ Date data type

[Anonymous]

When connecting to SQL Server (2014 standard edition) and choosing direct query date data type is represented as DateTime in powerBI. e.g. 4/15/2004 12:00:00 AM. Since DQ doesn't allow transformation it can't get this date to show only date portion.

Suggestions?

[v-yuezhe-msft]

Hi @Anonymous,

Firstly, I am not able to reproduce your issue that date data type is represented as DateTime in Power BI when connecting SQL Server 2014 Standard edition via Direct Query option, please check the following screenshots. Power BI Desktop doesn’t change the Date data type to DateTime. Please make sure that your columns are defined as Date data type in SQL Server.




Secondly, when we use Advanced filtering to filter a Date type field in Power BI Desktop, it still shows the time portion in visual level filter, from my point of view, this behavior is by design. I have reported this issue internally and will post back once I get any updates.

Thanks,
Lydia Zhang

[Anonymous]

Hi @v-yuezhe-msft,

Thanks for responding. This is definitely one of those "works on my machine" type thing. See the screenshot below. 

And the screen shot of PowerBI desktop here. Notice how it chagned it to DateTime:

Since this is a DirectQuery it won't let appy & save any transformation or datatype chagnes.

[v-yuezhe-msft · Accepted solution]

Hi @Anonymous,

When I write T-SQL statements to get data from SQL Server, I can reproduce your issue.


In this case, you can directly change the data type of the date column in Report View, please click the Date column in the Fields panel of the following screenshot, then you can click on Modeling in the Desktop ribbon and change its type to Date.


Also you can change the date format to your desired format.



Thanks,
Lydia Zhang

[Seth_C_Bauer]

@v-yuezhe-msft It doesn't seem very intuitive to me that even when I do change the data type/format in Power BI that the output in the preview appears as 9/8/2016 but when the field appears in the visual it shows up as Thursday, September 8th, 2016... I don't ever see using that format in a visual...

The date datatype from SQL always by default is showing up as 9/8/2016  12:00:00000 (or something to that effect.

[Seth_C_Bauer]

@Anonymous Add an additional column in the dataset that returns the date in text format.

[Anonymous]

@Seth_C_Bauer I've tried that and it works fine as a column but the issue is when you want to use that column a filter. Since it's text you can't perform any of the date filering. Workaround is to have one column for display (text) and another for filtering but this is a very dirty hack to accomplish a basic function and you still have the time portion that's always zero'd out in the filtering.

I've actually joined my entire Date dimension hoping to get around some of this stuff but I've not been successfull.

Just wondring if this is a known limitation.

[Seth_C_Bauer]

@Anonymous Must be.  I've found that you can change the data type on the direct query by clicking "Edit Queries" -> "Edit Queries" -> Highlight the column and change the data type, but even when selecting "Date" it looks right in the preview table pane, but when you go back to the visuals it shows up as (example: Sunday, January 1, 2012).

I was messing around with trying to force format in M, but it looks like this is a visual rendering issue with adjusting the format...

You could explore it further to see if I'm missing something in this section, but you do have access to modify the format in Direct Query using Edit Queries. And by opening "Advanced Editor" - you can modify the "M" code.