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”
FROM “MRANALYSTDATA_FOR_MR”
where 1=1 [[AND {{ErschDatum}}]]
GROUP BY “PRODUKT”
ORDER BY “PRODUKT” ASC
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?
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.