SQL SERVER – Information Related to DATETIME and DATETIME2
- by pinaldave
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)
Filed under: Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology
