Forum Discussion
SQL column as Date type format converting to Date/Time during Query Editor Import
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
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
8 Replies
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
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
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
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.
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.
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.
Anonymous as I understand Tgilchrist the problem appears already in Power Query.
