SQL column as Date type format converting to Date/Time during Query Editor Import

[Tgilchrist]

Hello all, 

As stated in the subject I'm having an issue with a SQL column formated as a Date type being recognized by the Query editor as Date/Time. 

I have already disabled both "Auto date/time" and "Automatically detect column types and headers for unstructured sources" under the current file date load options.

I've attached an example of my SQL Statement below... 

SELECT

     UniqueID,

     Cast(Timestamp as DATE) as JustDate,

     Cast(Timestamp as TIME) as JustTime

FROM Mydatabase.dbo.MyTable

Any ideas on how to prevent this from happening?

I know I can just convert the data type using M after loading the source but I would like to avoid the redundancy of repeating a data type conversion 

[SusieN]

Is there a solution to this?  I am using DirectQuery to pull SQL data.  Get the message that I cannot change the data type once the data has been applied because it is DirectQuery, so used Convert (date,dateField) in the query which results in a Date only field in SSMS when checking the results.  But in PowerBI insists on adding the 00:00 time element.  And I cannot seem to just format the table column to remove the time so looks really untidy.

[Anonymous]

@Tgilchrist: I would actually import those fields directly from SQL as DateTime, and then format the fields to show either or date or time. You will have to do this anyway to get the right format to show when you build the report.

[az38]

@Anonymous as I understand @Tgilchrist the problem appears already in Power Query.

[az38]

Hi @Tgilchrist 

Just curious, why is this such a problem?

anyway, try to play with CAST or CONVERT

I think

CAST(Timestamp as nvarchar(10))

could help you

[SusieN]

This does cause a problem when you want to use the date field with slicers or as values in tables - having the redundant time element is just messy and looks as though you can't be bothered to format properly.  Where in reality I can't find a way to format to just date.

[mwegener]

Hi @SusieN,

I had a similar problem this week.
May I ask what is your data source?
I have not been able to test it yet, but hope to solve it using the "Date Only" transformation.

[Tgilchrist]

It's not causing any problems with the report as far as performance or modeling goes, it just seems like a redundant thing to do and I don't have much experience in PowerBi so I was hoping it may be something simple. 

just testing a few combinations, It seems like nvarchar is always recognize as Text regardless if the content is in a date format and date is recognized as Date/Time

[mwegener · Accepted solution]

Hi @Tgilchrist ,

I found the following answer.

Date format is DateTime only set to midnight.

https://community.powerbi.com/t5/Desktop/Change-datetime-format-to-date-in-SQL/td-p/441602