A Brief Discussion on SQL Server Date/Time Queries and Precision Rounding Pitfalls

TLDR

• It is recommended to adopt datetime2 or datetimeoffset exclusively and avoid using datetime and smalldatetime.
• Regarding datetime2 precision settings, it is recommended to explicitly specify (0), (3), or (7) based on business requirements; do not rely on framework defaults.
• Time processing should ideally be completed at the application layer with a unified source to avoid time discrepancies between the database layer and the application layer.
• When performing time range queries, always use the Start <= Time < End pattern to avoid precision rounding and misjudgment issues caused by using AddTicks(-1) with <=.
• Avoid performing operations (such as DATEADD) on columns used in query conditions to ensure that indexes can be utilized effectively.
• When using Dapper or ADO.NET, pay attention to parameter type mapping to avoid implicit conversions or query failures caused by type mismatches.

---

SQL Server Date/Time Types

In SQL Server, the precision and applicable scenarios for different date/time types vary significantly.

Type	Precision	Description
datetime	3.33 milliseconds	A legacy type; milliseconds are not continuous and will automatically round up.
smalldatetime	1 minute	Very low precision; seconds are rounded to the nearest minute.
datetime2	100 nanoseconds	The preferred choice for new projects; high precision and wide range.
datetimeoffset	100 nanoseconds	Includes time zone offset, suitable for international applications.

TIP

In practice, smalldatetime and datetime should be completely deprecated. smalldatetime only supports dates up to the year 2079, posing a potential "Y2K" risk, while datetime has been superseded by datetime2. Furthermore, if migrating data from Oracle, datetime2 supports dates starting from the year 0001, avoiding the write limitation of datetime which only supports dates from 1753 onwards.

Choosing Precision: (0), (3), (7)

• Precision (0) (Seconds): Suitable for scenarios that do not require high-precision display, avoiding query failures caused by inconsistencies between front-end display and back-end storage.
• Precision (3) (Milliseconds): The industry standard, balancing performance and resolution.
• Precision (7) (100 Nanoseconds): The default value, suitable for scientific calculations, though one must ensure the hardware clock supports this resolution.

WARNING

If using Entity Framework Code First, be sure to explicitly specify the precision (e.g., .HasPrecision(0)) and do not rely on framework defaults to avoid unexpected behavior during future upgrades or migrations.

Choosing the Time Source: Application Layer vs. Database Layer

In non-distributed architectures, it is recommended to unify time processing at the application layer rather than relying on the database's GETDATE().

• Application Layer Processing: This avoids issues where the application server and database server clocks are out of sync.
• SARGability Considerations: Performing operations on columns (e.g., DATEADD(MINUTE, -1, RecordTime) = GETDATE()) will cause an Index Seek to degrade into an Index Scan and should be avoided.

Handling Time Range Boundaries

When performing time range queries, avoid using AddTicks(-1), as it easily leads to precision misjudgment.

Incorrect Example

// ❌ Not recommended: Prone to misjudgment due to precision differences
DateTime endTime = input.EndTime.AddTicks(-1);
db.Table.Where(x => x.Time >= input.StartTime && x.Time <= endTime);

Recommended Approach

// ✅ Recommended: Semantically clear and universally applicable
db.Table.Where(x => x.Time >= input.StartTime && x.Time < input.EndTime);

Pitfalls Caused by Time Precision

When a database column is of type datetime, its millisecond value will only be 0, 3, or 7. If the variable passed into the query condition has higher precision than the column (e.g., using a datetime2 variable to query a datetime column), SQL Server will automatically round the variable, leading to query results that differ from expectations.

Furthermore, if using Dapper or ADO.NET, the underlying type inference mechanism will convert parameters to datetime. If the type is not explicitly specified, it may lead to unexpected rounding errors in WHERE conditions. Therefore, consistently using the < operator for range queries is the best practice to avoid such precision traps.

Change Log

• 2026-02-12 Initial version created.