Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Tgilchrist
Frequent Visitor

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 

 

 

 

 

1 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

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

8 REPLIES 8
SusieN
Regular Visitor

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
Not applicable

@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.

Annotation 2020-05-28 141457.png

az38
Community Champion
Community Champion

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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
az38
Community Champion
Community Champion

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
SusieN
Regular Visitor

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.

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.

Date Only.pngDate Only.png

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


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

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.