Bug? Field Filter with date nor working with German Date format dd.mm.yyyy

Hi metabase fans and developers,

actual I recognized an issue with a field filter for a date range.
I’m using a SQL Server 2014 Table. In metabase I wrote this SQL query:

SELECT count(*) AS “Anzahl Clippings”, “PRODUKT” AS “Produkt”, sum(“Auflage”) AS “Auflage”
where 1=1 [[AND {{ErschDatum}}]]

The variable “ErschDatum” is a Field Filter for the column “Erscheindatum”, which is from datatype smalldatetime in the SQL-Server Table.
When I use the filter at the dashboard for a date range like " 1 January to 11 March" everything works fine. But as soon as I choose a date range like “1 January to 13 March” the widget says “There was a problem displaying the chart”.

Conclusion: When I choose a date range where the start or the end-date contains a day larger then 12, the error occurs.
This problem occurs only in SQL queries.

Is there a known problem with dateformats like dd.mm.yyyy?

Thanks & Regards

1 Like

Hmm, would you mind opening up a new github issue so we can try to figure out what’s going on here? Thanks!

1 Like

Opened a new issue on github. New issue #4320

Thanks & Regards

1 Like

I think I’ve found the cause of this error.
The statement metabase creates (BETWEEN ‘2016-01-01’ AND ‘2016-01-15’) assumes that the dateformat is MDY. But the language seeting of my database is german with a dateformat DMY. Because of this the metabase statement treats the month as day and vice versa.

Wouldn’t it be better that metabase creates a statement like BETWEEN ‘20160101’ AND ‘20160115’? Because this would be the ISO-format which is language independent.

In the meanwhile I’ve found the following workaround, that works for me.

In my SQL Server I added a new user “metabase” with the default language “englisch”.
In the Metabase Backend (Admin–>Databases) I changed the “Database username/password” to the new user.
Now everything is fine! The error is gone.