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.
Reference: Pinal Dave (http://www.SQLAuthority.com)
Dear Pinal,
I really enjoy with you question and answer. Also i improve each minutes. Today, i find out ways in my life. I can daily see your face and question and Answer
Thanks a lot.
Regards,
Ashokkumar A
[...] from a Datetime – Get Date Part Only from Datetime Datetime Function TODATETIMEOFFSET Example Information Related to DATETIME and DATETIME2 Datetime Function SWITCHOFFSET Example Difference Between DATETIME and DATETIME2 – WITH GETDATE [...]
[...] Difference between DATETIME and DATETIME2 Developers have found the root reason of the problem when dealing with Date Functions – when data time values are converted (implicit or explicit) between different data types, which would lose some precision, so the result cannot match each other as expected. In this blog post I go over very interesting details and difference between DATETIME and DATETIME2 [...]
[...] Information Related to DATETIME and DATETIME2 There are quite a lot of confusion with DATETIME and DATETIME2. DATETIME2 is also one of the underutilized datatype of SQL Server. In this blog post I have written a follow up of the my earlier datetime series where I clarify a few of the concepts related to datetime. [...]