cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
robarivas Member
Member

Dates coming in as Datetime

I'm pulling some date fields from IBM DB2. Need to pull the data by providing a SQL statement (long story, don't ask). However, when pulling data with a SQL statement Power Query adds 12:00:00 AM, which then forces me to have to change data type from Datetime to Date. This is a big problem because Power Query won't fold data type conversion back to DB2. Any thoughts on what I can do here? In my SQL statement I tried wrapping the date fields with the Date function but it didn't help.

3 REPLIES 3
v-fengyz-msft Frequent Visitor
Frequent Visitor

Re: Dates coming in as Datetime

@robarivas

 

Thanks for your question.

 

I can reproduce this issue when using IBM or Microsoft driver to retrieve data from DB2. As you stated, Power BI DeskTop will add the 12:00:00 AM for date type automatically when using SQL Statement.

 

Based on my test, you can use ODBC driver with SQL Statement as a workaround, which works properly.

 

And also, you can give an feedback to Microsoft on Power BI Ideas.

 

Thanks for your understanding and support.

 

Regards,

Pirlo Zhang 

 

robarivas Member
Member

Re: Dates coming in as Datetime

Thank you @v-fengyz-msft

 

Would you happen to know which of the 3 methods gives the overall best experience: ODBC, Microsoft driver, or IBM driver? Is there somewhere one could look to learn about the differences that exist, if any, amongst these three? I'm particularly interested in knowing which option does the most Query Folding. At a minimum I'd love to know about the differences between ODBC and the Microsoft driver since I've never been able to successfully connect using the IBM driver anyway.

v-fengyz-msft Frequent Visitor
Frequent Visitor

Re: Dates coming in as Datetime

@robarivas

 

Sorry, there are no official doucuments on these drivers to clarify the difference.

 

In my experience, in Power BI Desktop, I guess IBM driver  should be .NET oledb driver which produced by IBM. Microsoft driver represents Microsoft OLE DB Provider for DB2. 

 

From performance perspective, to extract big data using these drivers, in general the order of speed is: ODBC<IBM<Microsoft OLE DB Provider for DB2(fastest) when ignore other potential impact(e.g. network, I/O, memory..). 

 

However, In Power BI Desktop, in general we develop the report using small data and write the data into cache so we don't need to be afraid of the performance. Just select which works properly.

 

Regards,

Pirlo Zhang 

 

 

 

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 257 members 2,589 guests
Please welcome our newest community members: