Specific dates filter bug

KotoriKo · March 7, 2024, 5:20am

Hi!
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.

WHERE
  (
    "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...

WHERE
  (
    "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.

Luiggi · March 8, 2024, 12:47am

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

KotoriKo · March 11, 2024, 12:31am

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

notrom · March 11, 2024, 9:18pm

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.

KotoriKo · March 11, 2024, 9:25pm

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:

notrom · March 11, 2024, 9:56pm

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:

github.com/metabase/metabase

Date filters giving inconsistent results, depends on filter mode

opened 01:59AM - 01 May 23 UTC

closed 03:24PM - 12 Feb 24 UTC

notrom

Type:Bug Priority:P1 Database/SQLServer Querying/Processor .Correctness Misc/Timezones .Team/QueryProcessor

### Describe the bug If I use the date filter mode "After" for the first day in… the month I get what looks like the data I expect. However if I use "Between" with the same start date I'm getting records from the day before the start of that month. I've only seen this in data from our MSSQL data sources. This is likely timezone related, we're at GMT+12/13 which has confused Metabase before, but what I don't understand is why it's inconsistent. Using "After": ![image](https://user-images.githubusercontent.com/4504437/235387387-f669c1a9-ceaf-4af8-a465-c7e853a5e63d.png) Using "Between: ![image](https://user-images.githubusercontent.com/4504437/235387406-3067acce-0f0a-4781-b926-2ffdf0ef4a7f.png) Note that the second image above includes a count from June. Regardless of which filter mode I use above, clicking on the "View these XXX" from clicking on the July count bar takes me to this screen which includes the single record from June. ![image](https://user-images.githubusercontent.com/4504437/235387548-2a189771-e8db-49df-9b60-a6ae1c01d2be.png) ### To Reproduce I don't have a good repro on the sample database as I think this may be an MS SQL Server specific issue. For a SQL Server connection this is a repro ... 1. Ensure you have data within the GMT offset hours prior to the start of a month e.g. 30 June, 23:00 if filter date will be 1 July. 2. Create a question counting rows per month using the between filter starting on the first day of the month used above, e.g. 1 July 3. You'll get at least one count reported in June ### Expected behavior Date filters should be consistent and correctly applied. ### Logs Nothing in the logs but these are the generated SQL queries (redacted, only relevant date specific parts) Using After: ```sql SELECT DateFromParts(year("xxx"."xxx"."DateReceived"), month("xxx"."xxx"."DateReceived"), 1) AS "DateReceived", count(*) AS "count" FROM "xxx"."xxx" LEFT JOIN "yyy"."yyy" WHERE (qqq AND "xxx"."xxx"."DateReceived" >= DateTimeOffsetFromParts(2022, 7, 2, 0, 0, 0, 0, 12, 0, 7)) GROUP BY year("xxx"."xxx"."DateReceived"), month("xxx"."xxx"."DateReceived") ORDER BY year("xxx"."xxx"."DateReceived") ASC, month("xxx"."xxx"."DateReceived") ASC ``` Using "Between": ```sql SELECT DateFromParts(year("xxx"."xxx"."DateReceived"), month("xxx"."xxx"."DateReceived"), 1) AS "DateReceived", count(*) AS "count" FROM "xxx"."xxx" LEFT JOIN "yyy"."yyy" WHERE (qqq AND "xxx"."xxx"."DateReceived" >= DateTimeOffsetFromParts(2022, 7, 1, 0, 0, 0, 0, 12, 0, 7) AND "xxx"."xxx"."DateReceived" < DateTimeOffsetFromParts(2023, 5, 2, 0, 0, 0, 0, 12, 0, 7)) GROUP BY year("xxx"."xxx"."DateReceived"), month("xxx"."xxx"."DateReceived") ORDER BY year("xxx"."xxx"."DateReceived") ASC, month("xxx"."xxx"."DateReceived") ASC ``` And for the drill through detail table: ```sql SELECT TOP 1048575 "xxx"."xxx"."DateReceived" AS "DateReceived" FROM "xxx"."xxx" WHERE (qqq AND "xxx"."xxx"."DateReceived" >= DateTimeOffsetFromParts(2022, 7, 1, 0, 0, 0, 0, 12, 0, 7) AND "xxx"."xxx"."DateReceived" < DateTimeOffsetFromParts(2022, 8, 1, 0, 0, 0, 0, 12, 0, 7)) ``` You can see these use a mixture of `DateTimeOffsetFromParts(2022, 7, 1, 0, 0, 0, 0, 12, 0, 7)` and `DateTimeOffsetFromParts(2022, 7, 2, 0, 0, 0, 0, 12, 0, 7)`. ### Information about your Metabase installation ```JSON { "browser-info": { "language": "en-US", "platform": "Win32", "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:109.0) Gecko/20100101 Firefox/112.0", "vendor": "" }, "system-info": { "file.encoding": "Cp1252", "java.runtime.name": "OpenJDK Runtime Environment", "java.runtime.version": "17.0.2+8", "java.vendor": "Eclipse Adoptium", "java.vendor.url": "https://adoptium.net/", "java.version": "17.0.2", "java.vm.name": "OpenJDK 64-Bit Server VM", "java.vm.version": "17.0.2+8", "os.name": "Windows Server 2012 R2", "os.version": "6.3", "user.language": "en", "user.timezone": "Pacific/Auckland" }, "metabase-info": { "databases": [ "sqlserver", "postgres", "h2" ], "hosting-env": "unknown", "application-database": "postgres", "application-database-details": { "database": { "name": "PostgreSQL", "version": "10.5" }, "jdbc-driver": { "name": "PostgreSQL JDBC Driver", "version": "42.5.0" } }, "run-mode": "prod", "version": { "date": "2023-02-19", "tag": "v0.45.3", "branch": "release-x.45.x", "hash": "070f57b" }, "settings": { "report-timezone": "Pacific/Auckland" } } } ``` ### Severity This is blocking some users or causing them to have to double check data. It's giving inconsistent data, sometimes applying filters incorrectly. ### Additional context This may be a regression but I can't find specific issues relating to this.

github.com/metabase/metabase

Date filtering change between v0.48.3 and v0.48.4. looks timezone related

opened 02:36AM - 30 Jan 24 UTC

closed 07:06PM - 15 Feb 24 UTC

notrom

Type:Bug Priority:P1 Querying/Processor .Correctness Misc/Timezones .Team/QueryProcessor

### Describe the bug I upgraded from v0.48.3 to v0.48.4 and I'm now getting dif…ferent `where` clauses in Metabase generated SQL Server queries and different results. v0.48.4 appears to be incorrect. There have been no changes to config or questions, but I'm getting different results. This is the datetime field filter in the UI: ![image](https://github.com/metabase/metabase/assets/4504437/81a82209-edfe-4a94-95b9-2383b503b0f6) ## v0.48.3 gave me this: ```sql WHERE ( "source"."d" >= DateTimeOffsetFromParts(2023, 8, 28, 0, 0, 0, 0, 0, 0, 7) AND "source"."d" < DateTimeOffsetFromParts(2023, 8, 30, 0, 0, 0, 0, 0, 0, 7) ) ``` ## v0.48.4 gives me this: ```sql WHERE ( "source"."d" >= DateTimeOffsetFromParts(2023, 8, 28, 0, 0, 0, 0, 13, 0, 7) AND "source"."d" < DateTimeOffsetFromParts(2023, 8, 30, 0, 0, 0, 0, 13, 0, 7) ) ``` Database is MS SQL Server. Metabase report timezone is "Pacific/Auckland" (although this doesn't matter as field is datetime so no tz stored). Java timezone set to UTC via command line at startup (to give correct behaviour for MS SQL Server datetimes). ### To Reproduce 1. In 0.48.7, using MS SQL datasource, create a question from this SQL: <details> <summary>Hours query, every hour for 27, 28, and 29 August 2023</summary> ```sql select cast('2023-08-27 00:00' as datetime) d union all select cast('2023-08-27 01:00' as datetime) d union all select cast('2023-08-27 02:00' as datetime) d union all select cast('2023-08-27 03:00' as datetime) d union all select cast('2023-08-27 04:00' as datetime) d union all select cast('2023-08-27 05:00' as datetime) d union all select cast('2023-08-27 06:00' as datetime) d union all select cast('2023-08-27 07:00' as datetime) d union all select cast('2023-08-27 08:00' as datetime) d union all select cast('2023-08-27 09:00' as datetime) d union all select cast('2023-08-27 10:00' as datetime) d union all select cast('2023-08-27 11:00' as datetime) d union all select cast('2023-08-27 12:00' as datetime) d union all select cast('2023-08-27 13:00' as datetime) d union all select cast('2023-08-27 14:00' as datetime) d union all select cast('2023-08-27 15:00' as datetime) d union all select cast('2023-08-27 16:00' as datetime) d union all select cast('2023-08-27 17:00' as datetime) d union all select cast('2023-08-27 18:00' as datetime) d union all select cast('2023-08-27 19:00' as datetime) d union all select cast('2023-08-27 20:00' as datetime) d union all select cast('2023-08-27 21:00' as datetime) d union all select cast('2023-08-27 22:00' as datetime) d union all select cast('2023-08-27 23:00' as datetime) d union all select cast('2023-08-28 00:00' as datetime) d union all select cast('2023-08-28 01:00' as datetime) d union all select cast('2023-08-28 02:00' as datetime) d union all select cast('2023-08-28 03:00' as datetime) d union all select cast('2023-08-28 04:00' as datetime) d union all select cast('2023-08-28 05:00' as datetime) d union all select cast('2023-08-28 06:00' as datetime) d union all select cast('2023-08-28 07:00' as datetime) d union all select cast('2023-08-28 08:00' as datetime) d union all select cast('2023-08-28 09:00' as datetime) d union all select cast('2023-08-28 10:00' as datetime) d union all select cast('2023-08-28 11:00' as datetime) d union all select cast('2023-08-28 12:00' as datetime) d union all select cast('2023-08-28 13:00' as datetime) d union all select cast('2023-08-28 14:00' as datetime) d union all select cast('2023-08-28 15:00' as datetime) d union all select cast('2023-08-28 16:00' as datetime) d union all select cast('2023-08-28 17:00' as datetime) d union all select cast('2023-08-28 18:00' as datetime) d union all select cast('2023-08-28 19:00' as datetime) d union all select cast('2023-08-28 20:00' as datetime) d union all select cast('2023-08-28 21:00' as datetime) d union all select cast('2023-08-28 22:00' as datetime) d union all select cast('2023-08-28 23:00' as datetime) d union all select cast('2023-08-29 00:00' as datetime) d union all select cast('2023-08-29 01:00' as datetime) d union all select cast('2023-08-29 02:00' as datetime) d union all select cast('2023-08-29 03:00' as datetime) d union all select cast('2023-08-29 04:00' as datetime) d union all select cast('2023-08-29 05:00' as datetime) d union all select cast('2023-08-29 06:00' as datetime) d union all select cast('2023-08-29 07:00' as datetime) d union all select cast('2023-08-29 08:00' as datetime) d union all select cast('2023-08-29 09:00' as datetime) d union all select cast('2023-08-29 10:00' as datetime) d union all select cast('2023-08-29 11:00' as datetime) d union all select cast('2023-08-29 12:00' as datetime) d union all select cast('2023-08-29 13:00' as datetime) d union all select cast('2023-08-29 14:00' as datetime) d union all select cast('2023-08-29 15:00' as datetime) d union all select cast('2023-08-29 16:00' as datetime) d union all select cast('2023-08-29 17:00' as datetime) d union all select cast('2023-08-29 18:00' as datetime) d union all select cast('2023-08-29 19:00' as datetime) d union all select cast('2023-08-29 20:00' as datetime) d union all select cast('2023-08-29 21:00' as datetime) d union all select cast('2023-08-29 22:00' as datetime) d union all select cast('2023-08-29 23:00' as datetime) d ``` </details> 3. Create a new GUI question from that saved question 4. Filter by the "d" datetime for the 2 days 28th and 29th ![image](https://github.com/metabase/metabase/assets/4504437/056ceb0b-4929-4c31-96a6-1cc8159052df) 5. Returned data will incorrectly include records with a d datetime in the 27th in addition to the 28th and 29th i.e. 3 days. ![image](https://github.com/metabase/metabase/assets/4504437/bafee186-2676-465b-a69b-d67ef55c18cb) 6. Revert back to v0.48.3, rerun the filtered question and it correctly returns only records on the 28th and 29th ![image](https://github.com/metabase/metabase/assets/4504437/1454b9d8-2808-4561-a7f6-23d1343566a2) ### Expected behavior Date filtering shouldn't change unexpectedly between releases. Date filters should only return dates in the selected range. ### Logs _No response_ ### Information about your Metabase installation ```JSON { "browser-info": { "language": "en-NZ", "platform": "Win32", "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36", "vendor": "Google Inc." }, "system-info": { "file.encoding": "Cp1252", "java.runtime.name": "OpenJDK Runtime Environment", "java.runtime.version": "17.0.2+8", "java.vendor": "Eclipse Adoptium", "java.vendor.url": "https://adoptium.net/", "java.version": "17.0.2", "java.vm.name": "OpenJDK 64-Bit Server VM", "java.vm.version": "17.0.2+8", "os.name": "Windows 10", "os.version": "10.0", "user.language": "en", "user.timezone": "UTC" }, "metabase-info": { "databases": [ "sqlserver", "postgres", "h2" ], "hosting-env": "unknown", "application-database": "postgres", "application-database-details": { "database": { "name": "PostgreSQL", "version": "12.1" }, "jdbc-driver": { "name": "PostgreSQL JDBC Driver", "version": "42.6.0" } }, "run-mode": "prod", "version": { "date": "2024-01-29", "tag": "v0.48.4", "hash": "62145b0" }, "settings": { "report-timezone": "Pacific/Auckland" } } } ``` ### Severity Blocker for me moving to v0.48.4. Date filters are critical for both correctness and building trust with my users. ### Additional context I've previously spent a long time wrestling with timezones before I realised I had to explicitly set my Java timezone to UTC for date filters to works as expected against my MS SQL Server datetimes (all the dates we use). I have a check on my "always up" dashboard that checks if this is working as I expect. Metabase report timezone has no impact on this. I am in New Zealand, currently +13 hours on UTC so this difference is significant.

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:

KotoriKo · March 12, 2024, 10:16pm

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.

github.com/metabase/metabase

Specific dates filter incorrect result

opened 01:30AM - 12 Mar 24 UTC

SeshTiliRest

Type:Bug .Needs Triage

### Describe the bug Hi! I create a new question and select a table from my M…S SQL db. Add a filter by date. Specific dates... Between March 1 - 2. Preview and visualization in table shows wrong result. 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. 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. Preview of Specific dates show incorrect result: ![1](https://github.com/metabase/metabase/assets/28322040/967b20ee-fbea-4bac-8e1d-8b74522e4b66) Visualization with Specific dates filter show 3 Jan - 2 March 2024 (1,154 rows) instead of March 1-2, 2024 (31 rows): ![2](https://github.com/metabase/metabase/assets/28322040/f87f4d5d-4e2d-44ac-b2c8-bb9f8d1cce30) Also here is the query generated in the question: ![3](https://github.com/metabase/metabase/assets/28322040/6df5bb84-aa22-417f-8743-adb15d726acf) 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... ![4](https://github.com/metabase/metabase/assets/28322040/9a7e042a-fcd6-4604-9566-488ae6fa55aa) Yesteday and others date filters works fine, problem with Specific dates... ![5](https://github.com/metabase/metabase/assets/28322040/24f0ce2c-7853-4b28-847a-2244bab74971) I tried to select only March 15th and got the following error: ![6](https://github.com/metabase/metabase/assets/28322040/e93532c3-2d50-4a16-be92-3952064cae7f) ### To Reproduce 1. Login in 2. Click on + New 3. Select Question 4. Pick starting data (MS SQL db) 5. Select table 6. Click add filters 7. Select column 8. Click Specific dates... 9. Choose dates 1 March, 2024 - 2 March, 2024 10. Click Add filter 11. Click Preview 12. See incorrect result 13. Click Visualize 14. See incorrect result ### Expected behavior After filtering by Specific dates, a result should be displayed that corresponds to the filter by a Specific date ### Logs 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 ### Information about your Metabase installation ```JSON { "browser-info": { "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36", "vendor": "Google Inc." }, "system-info": { "file.encoding": "UTF-8", "java.runtime.name": "OpenJDK Runtime Environment", "java.runtime.version": "11.0.22+7", "java.vendor": "Eclipse Adoptium", "java.vendor.url": "https://adoptium.net/", "java.version": "11.0.22", "java.vm.name": "OpenJDK 64-Bit Server VM", "java.vm.version": "11.0.22+7", "os.name": "Linux", "os.version": "5.15.0-84-generic", "user.language": "en", "user.timezone": "GMT" }, "metabase-info": { "databases": [ "h2", "sqlserver" ], "hosting-env": "unknown", "application-database": "postgres", "application-database-details": { "database": { "name": "PostgreSQL", "version": "16.2 (Debian 16.2-1.pgdg120+2)" }, "jdbc-driver": { "name": "PostgreSQL JDBC Driver", "version": "42.6.0" } }, "run-mode": "prod", "version": { "date": "2024-03-04", "tag": "v0.48.8", "hash": "a900c85" }, "settings": { "report-timezone": "Asia/Sakhalin" } } } ``` ### Severity Broken dashboard with incorrect results ### Additional context Metabase Discuss link: https://discourse.metabase.com/t/specific-dates-filter-bug/85926

AndrewMBaines · March 13, 2024, 9:48am

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.

notrom · March 13, 2024, 9:11pm

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.

SSMS:

Metabase:

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

KotoriKo · March 13, 2024, 9:15pm

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:

SELECT
  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:

SELECT
  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.

AndrewMBaines · March 14, 2024, 7:59am

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.

KotoriKo · March 19, 2024, 9:28pm

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.

KotoriKo · March 28, 2024, 10:49pm

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

nckrishna · March 29, 2024, 7:58am

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