SQL SERVER – Information Related to DATETIME and DATETIME2

I recently received interesting comment on the blog regarding workaround to overcome the precision issue while dealing with DATETIME and DATETIME2.

I have written over this subject earlier over here.

SQL SERVER – Difference Between GETDATE and SYSDATETIME

SQL SERVER – Difference Between DATETIME and DATETIME2 – WITH GETDATE

SQL SERVER – Difference Between DATETIME and DATETIME2

SQL Expert Jing Sheng Zhong has left following comment:

The issue you found in SQL server new datetime type is related time source function precision. Folks have found the root reason of the problem – when data time values are converted (implicit or explicit) between different data type, which would lose some precision, so the result cannot match each other as thought. Here I would like to gave a work around solution to solve the problem which the developers met.

-- Declare and loop
DECLARE @Intveral INT, @CurDate DATETIMEOFFSET;
CREATE TABLE #TimeTable (FirstDate DATETIME, LastDate DATETIME2, GlobalDate DATETIMEOFFSET)
SET @Intveral = 10000
WHILE (@Intveral > 0)
BEGIN
----SET @CurDate = SYSDATETIMEOFFSET(); -- higher precision for future use only
SET @CurDate = TODATETIMEOFFSET(GETDATE(),DATEDIFF(N,GETUTCDATE(),GETDATE())); -- lower precision to match exited date process
INSERT #TimeTable (FirstDate, LastDate, GlobalDate)
VALUES (@CurDate, @CurDate, @CurDate)
SET @Intveral = @Intveral - 1
END
GO
-- Distinct Values
SELECT COUNT(DISTINCT FirstDate) D_DATETIME,
COUNT(DISTINCT LastDate) D_DATETIME2,
COUNT(DISTINCT GlobalDate) D_SYSGETDATE
FROM #TimeTable
GO
-- 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)
GO
-- Select
SELECT *
FROM #TimeTable
GO
-- Clean up
DROP TABLE #TimeTable
GO

If you read my blog SQL SERVER – Difference Between DATETIME and DATETIME2 you will notice that I have achieved the same using GETDATE().

Are you using DATETIME2 in your production environment? If yes, I am interested to know the use case.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.