Problem:
Creating an Ad Hoc filter using a SQL Server datetime field includes results for two days, today and tomorrow, even when we provide a relative date "DAY" as the filter value. The same works fine with the SQL Server date field instead of the datetime field. What could cause this issue?
Cause:
The datetime in SQL Server has an accuracy of 3.33ms (0.003 seconds) - therefore, the "highest" possible value for Feb 27, 2024, for example, would be 2024-02-27T23:59:59.997 - anything beyond that will be rounded up to the next day.
Solution:
The resulting Ad Hoc query when using the above SQL Server datetime field as a filter includes "table_name"."column_name"
BETWEEN { TS '2024-02-27 00:00:00' } AND { TS '2024-02-27 23:59:59.999' }
When we remove the .999 from the endpoint timestamp and run the query against the database it calculates correctly and the data showed in the database is for DAY instead of today and tomorrow.
Unfortunately, this is a SQL Server limitation and a recommended work around would be to use a date data type field instead of the datetime data type field to get the correct results in the Ad Hoc View.
Further read:
https://www.sqlshack.com/sql-server-datetime-data-type-considerations-and-limitations/
________________________________________________________________________________________________________________________
Ref. Case #02241584
-
SQL Server datetime Ad Hoc filter including today and tomorrow results even when a relative date "DAY" filter value is used
Table of contents
User Feedback
Recommended Comments
There are no comments to display.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now