cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
keithpoplar
Frequent Visitor

Power BI Desktop Date conversion error in French environment

 I have a report with date range as filters that works fine in the English environment. But it is causing an error in the French environment when the date filter is applied. 

 

With SQL Profiler, I found that Power BI is sending "CAST( N'2017-07-14 00:00:01' AS datetime)" command to SQL server. However, in the French environment, the SQL server is interpreting 14 as the month and failed to convert the string to date. Hence, Power BI desktop failed to visualize data.

 

How do I force Power BI sending date in ISO format? e.g. CAST( N'20170714' AS datetime)

5 REPLIES 5
v-huizhn-msft
Microsoft
Microsoft

Hi @keithpoplar,

First, in your french environment, restart your Power Bi desktop->files->Options and settings->Regional Settings->select your local based on your french environment as follows.

1.PNG

Second, when you get data from SQL Server, edit the query navigator, right click the date column->Change type-> Using local->Ok as follows, you will get expected date format.

3.png


More details, please review this article for further analysis.

Best Regards,
Angelia


Thank you very much for the reply. I tried these settings. It only changes the data format displayed in Power BI. But it doesn't change the date format in the query that was sent to SQL server when I apply a filter in Power BI desktop.

Hi @keithpoplar,

>> the date format in the query that was sent to SQL server  

What's the mean of "sent to", it refers to the data was get from SQL Server, right?

Best Regards,
Angelia

Hi @v-huizhn-msft let me explain more:

 

My report connects to SQL server and gets data from a SQL function call "getData".

And I have filters in my report that is pointing to a date time field of the SQL function. When I click the filters in Power BI desktop

When I click the filters in Power BI desktop, I can see a bunch of scripts sent to SQL server using SQL profiler. I guess these scripts were generated by Power BI desktop.

These scripts wrapped around my "getData" function and has cast statement in the where clause to filter data by date and time.

It looks like below code. That cast statement caused the error in SQL server with French OS. As this SQL is not controlled by me, I don't know how to fix the error.

 

Thank you.

 

([t8].[DateTime] < CAST( N'2016-06-16 08:00:00' AS datetime))
 AND 
([t8].[DateTime] >= CAST( N'2016-06-01 08:00:00' AS datetime))

 

sql profilersql profiler

 

cs_skit
Resolver IV
Resolver IV

for SQL connections you can choose language in Connections and Users

 

if its a non english SQL then usually default language is non english like here in a newly created user on a german server

If you cant change language in the connection in your case another possibility is create a different user with default language english

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors