DirectQuery wrong datetime format in filter

[umperio]

I tracked down my error to the following example.

I create a new report wich queries a view with only one column containing all the dates since January 2012 until today.

The column format is set to date only (actually datetimes displayed in italian locale dd/MM/yyyy)

If I add a slicer on the same field, I get unexpected results, even if the calendar on the slicer is perfectly localized.

As you see if I select from the 1st of May to the 1st of July ("luglio" in italian), I get the dates from the 5th of January to the 7th of February as if day and month have been swapped.

Profiling the query gives me the following, where a non ISO datetime string is going to be cast to datetime (of course if I select the last day of May the query will throw an exception as it does in PowerBI. Removing the dashes from the datetime strings of course fixes the issue.

SELECT
TOP (501) [t0].[ReferenceDate]
FROM
(
(select convert(date, [_].[ReferenceDate]) as [ReferenceDate]
from [dbo].[udv_DATE] as [_])
)
AS [t0]
WHERE
(
([t0].[ReferenceDate] <= CAST( N'2013-07-02 00:00:00' AS datetime))
AND
([t0].[ReferenceDate] >= CAST( N'2013-05-01 00:00:00' AS datetime))
)

GROUP BY [t0].[ReferenceDate]
ORDER BY
COALESCE([t0].[ReferenceDate], CAST(-2 AS datetime))

ASC

, (CASE WHEN [t0].[ReferenceDate] IS NULL THEN 0 ELSE 1 END)

ASC

What's also weird is that on the staging system with should be similar, the exact same query is executed, but is interpreted correctly.

Now I wonder if you have hints, suggestions or ideas.

In particular I wonder why the query doesn't use the ISO datetime string or if there's a way to force the format when producing the query.

Thanks

[v-caliao-msft]

Hi @umperio,

The date format is is controlled by the Locale Setting. MM/DD/YYYY is the US format. In your scenario,your date came form a place where they use DD/MM/YYYY. 

To have PBI recognize those as dates Go to File => Options and settings =>  Options => Locale => change to your

country/region

Regards,

Charlie Liao

[umperio]

I already tried that and didn't work: i changed the setting from the default to italian and restarted PowerBI, but nothing changed.

Also that settings doesn't seem to interact with how the query is built, in fact I see from T-SQL for CAST documentation that it's using ODBC datetime format:

-	20 or 120 (2)	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
-	21 or 121 (2)	ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset	yyyy-mm-dd hh:mi:ss.mmm(24h)

I wonder why since I'm connecting directly to a SQL server, not through ODBC.

Can you confirm you get the same results when you DirectQuery against a SQL Server 2008 R2?

Is there a way to force the standard of the datetime string, or force PowerBI/DirectQuery to know this is a SQL server connection and not an ODBC one?

Regards

[umperio · Accepted solution]

Since I couldn't find a solution and needed to deploy, I switched back to Import mode instead of Direct Query, and everything worked back again.

I hope it will help someone while the query filters get fixed.

Regards