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

Read more: Journey to SQL Authority with Pinal Dave

Posted via email from .NET Info