When working with dates and times in SQL Server, it is important to be aware of the precision issues that can arise. One such issue is the difference between the DATETIME and DATETIME2 data types.

In a recent comment on our blog, SQL expert Jing Sheng Zhong highlighted the precision problem that can occur when converting between these two data types. The root cause of the problem lies in the loss of precision during the conversion process, resulting in unexpected results.

To address this issue, Zhong suggests a workaround solution that developers can use to overcome the precision problem. Here is an example of how it can be implemented:

-- Declare and loop
DECLARE @Interval INT, @CurDate DATETIMEOFFSET;
CREATE TABLE #TimeTable (FirstDate DATETIME, LastDate DATETIME2, GlobalDate DATETIMEOFFSET)
SET @Interval = 10000
WHILE (@Interval > 0)
BEGIN
    SET @CurDate = TODATETIMEOFFSET(GETDATE(),DATEDIFF(N,GETUTCDATE(),GETDATE()));
    INSERT #TimeTable (FirstDate, LastDate, GlobalDate)
    VALUES (@CurDate, @CurDate, @CurDate)
    SET @Interval = @Interval - 1
END

-- Distinct Values
SELECT COUNT(DISTINCT FirstDate) D_DATETIME,
COUNT(DISTINCT LastDate) D_DATETIME2,
COUNT(DISTINCT GlobalDate) D_SYSGETDATE
FROM #TimeTable

-- Join
SELECT DISTINCT a.FirstDate,b.LastDate, b.GlobalDate, CAST(b.GlobalDate AS DATETIME) GlobalDateASDateTime
FROM #TimeTable a
INNER JOIN #TimeTable b ON a.FirstDate = CAST(b.GlobalDate AS DATETIME)

-- Select
SELECT *
FROM #TimeTable

-- Clean up
DROP TABLE #TimeTable

By using this workaround, developers can ensure that the precision issue is addressed when working with DATETIME and DATETIME2 data types.

If you are using DATETIME2 in your production environment, it is important to consider the use case and evaluate whether the precision issue may impact your application. Understanding the differences between these data types and implementing appropriate workarounds can help ensure accurate and reliable results.

Stay tuned for more SQL Server tips and tricks on our blog!