Specific dates filter bug

I create a new question and select a table.
Add a filter by date. Specific dates... Between March 1 - 2.
The preview shows the correct selection. Approximately 15 rows.
When visualized in the table, it shows many more rows (~100) that do not match the filter that I set.

I copied the generated sql query, testing query in HeidiSQL and it works correctly, same as in the preview.

    "dbo"."Plan"."DateTake" >= '2024-03-01 00:00:00'
   AND "dbo"."Plan"."DateTake" < '2024-03-02 00:00:00'

Also in the visualization everything is displayed correctly if i select Today, Yesterday but not when I select Specific dates...

    "dbo"."Plan"."DateTake" >= DateFromParts(
      year(dateadd(day, -1, getdate())),
      month(dateadd(day, -1, getdate())),
      day(dateadd(day, -1, getdate()))
   AND "dbo"."Plan"."DateTake" < DateFromParts(year(getdate()), month(getdate()), day(getdate()))

Metabase version v0.48.8 in docker with MSSQL db connected.
I didn't see this problem in previous versions.

are you able to send a video? what's the DW?

Hi! It looks like I was wrong, the preview also shows the wrong result.
Recording a video is problematic, but here are screenshots
where you can see that dates are displayed that do not match the filter March 1-2, 2024,
shows 3 Jan - 2 March 2024 (1,154 rows).
The DateTakeoff field with SMALLDATETIME type contains local time,
same as on a virtual machine with Docker, Metabase + Postgres containers.
What does DW mean?

Preview of Specific dates show incorrect result:

Visualization with Specific dates filter show 3 Jan - 2 March 2024 (1,154 rows)
instead of March 1-2, 2024 (31 rows):

Also here is the query generated in the question:

And here is the result of this query in HeidiSQL
which makes the selection correctly March 1-2, 2024 (31 rows)
I tried to create +New -> SQL Query in Metabase and insert the same query and result shows the wrong...

Yesteday and others date filters works fine, problem with Specific dates...

Warnings in log:

WARN metabase.driver.sql-jdbc.sync.describe-table Don't know how to map column type 'tinyint identity' to a Field base_type, falling back to :type/*
WARN metabase.driver.sql-jdbc.sync.describe-table Don't know how to map column type 'smallint identity' to a Field base_type, falling back to :type/*
WARN metabase.driver.sql-jdbc.sync.describe-table Don't know how to map column type 'sysname' to a Field base_type, falling back to :type/*
WARN metabase.driver.sql.query-processor.deprecated Warning: The :sqlserver driver uses Honey SQL 1. This method was deprecated in 0.46.0 and will be removed in a future release

Looks like a language or date format issue.

Default MS SQL datetime string format is "YYYY-MM-DD HH:MI:SS".

Looks like Metabase is building the filter date string for what you asked for in the Metabase date filter GUI, based on that format.

However you may have your SQL server(or the user Metabase uses) configured to expect date strings in a different format. SQL Server seems to be parsing the date strings with flipped MM and DD.

A quick way to verify this could be to do a Metabase data filter for 15th March 2024, if your DB can't turn that into a valid date and reports an error then it's used "15" for the month.

I'm not sure of all the interactions between MS SQL language, date format, timezone, Metabase language, date format, report timezone, and Java timezone. Hopefully the Metabase team can point you in the right direction.

Hope that helps somewhat, or I could be totally on the wrong track.

1 Like

Hi! Thank you very much for the answer!
The strangest thing is that in previous releases everything worked correctly, and then, after several updates, I noticed that my dashboard was displaying incorrect information with this specific date filter.
I'm sure that in version 0.48.1 everything worked correctly.

I tried to select only March 15th and got the following error:

There has been a change at around v0.48.6 for a long standing issue around MS SQL specific date filters applying a timezone when it's not appropriate (to datetime fields which have no timezone). It could well be related to that.

For reference, specific date filters are working for me as expected on v0.48.8 against MS SQL.

I'd suggest creating an issue in Github with the screen shots etc. you've included above.

Issues recently closed:

The code change, it looks like it's explicitly assuming the "YYYY-MM-DD HH:MI:SS" format, although I'm neither a Metabase or Clojure programmer so can't really comment:

1 Like

Thanks! I created a issue on GitHub.
Looks like month and day in the date format are reversed.
In my db all datetime fields in this format YYYY-MM-DD.
But it seems the query processes the date in the format YYYY-DD-MM.

Ugh - this is a horrible bug from Metabase.

Try this query:

select month(cast('2024-03-01 00:00:00' as datetime)) as full_date_time,  month(cast('2024-03-01' as date)) as just_date

In SSMS, you'll get 3,3 (correct)
In Metabase, you'll get 1,3

Metabase gets the date order wrong when you use a date time rather than just a date. It's not SQL server - there's no language setting for yyyy-dd-mm!

As a workaround, what happens if you add an extra column of just the date and use that instead? Make sure you set the Metabase type to date too.

I've added the query to your GitHub issue.

1 Like

Wow that's really wrong then!

I'm not seeing this issue either using filters in the GUI or when using that SQL, both SSMS and Metabase (v0.48.8) give me the correct answer 3,3.



So although it's clearly wrong, it doesn't appear to be universally wrong, there must be something else going on.

1 Like

Yo! Yah you are wright!

Metabase SQL query shows full_date_time 1 and just_date 3 :

HeidiSQL shows full_date_time 3 and just_date 3:

In Metabase this SQL query shows 1 and 3:

  DATEPART(MONTH, CAST('2024-03-01 00:00:00' AS DATETIME)) AS full_date_time,
  DATEPART(MONTH, CAST('2024-03-01' AS DATE)) AS just_date

And in Metabase this SQL query shows 3 and 3:

  MONTH(CONVERT(datetime, '2024-03-01 00:00:00', 120)) AS full_date_time,
  MONTH(CONVERT(date, '2024-03-01', 120)) AS just_date

In this query, the CONVERT() function takes a third parameter - the style of the date format.
Style 120 is used for the universal datetime format (YYYY-MM-DD HH:MI:SS), which should be recognized independently of regional settings.

It looks like it's to do with Metabase and SQL server having different locales.
In the UK, SQL Server is normally left at US English as that's the default. That means that formatting a date is mdy.
However, the OS and Metabase are both set to use British English giving dmy.
Try this:

I get 3,3,1 in SQL
but 1,3,3 in Metabase.

Metabase is using the OS locale to cast the dates. Makes sense except that yyyy-dd-mm hh:MM:ss isn't a valid format and that's what Metabase is trying to use.

If it makes any difference, my test system is all Windows.

1 Like

Yah, i get same result with this query.
select month(cast('2024-03-01 00:00:00' as datetime)) as full_date_time, month(cast('2024-03-01' as date)) as just_date,month(cast('01-03-2024' as date)) as dateFormatCheck
3,3,1 in SQL and 1,3,3 in Metabase.
Testing client OS is Windows with DD-MM-YYYY date format. Same on MS SQL server.

Hi! As far as I can see, after updating to v0.49.2 I no longer see this problem. Thank you!

Even after v0.49.2 we are facing date filter issue. Date filter “Between” is incorrectly giving me data outside of the date range