cancel
Showing results for 
Search instead for 
Did you mean: 
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 work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


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.

toolatejake
Helper V
Helper V

@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

@toolatejake 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
Super User II
Super User II

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 work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


Tgilchrist
Frequent Visitor

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 work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors