Forum Discussion

keithpoplar's avatar
keithpoplar
Frequent Visitor
9 years ago

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...
Need Help
v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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.



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.




More details, please review this article for further analysis.

Best Regards,
Angelia


keithpoplar's avatar
keithpoplar
Frequent Visitor
9 years ago

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.

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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

    • keithpoplar's avatar
      keithpoplar
      Frequent Visitor
      9 years ago

      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 profiler