Yesterday I have written a very quick blog post on SQL SERVER – Difference Between GETDATE and SYSDATETIME and I got tremendous response for the same. I suggest you read that blog post before continuing with this blog post today. I had asked people to honestly take part and share their view about the above two system functions.
There are few emails as well as few comments on the blog post asking a question on how did I come to know the difference between the same. The answer is from real world issues. I was called in for performance tuning consultancy, where I was asked a very strange question by one developer. Here is the situation faced by him.
System had a single table with two different columns of datetime. One column was datelastmodified and the second column was datefirstmodified. One of the columns was DATETIME and the other was DATETIME2. Developer was populating each of them with SYSDATETIME. He assumed that the value inserted in the table will be the same. This table was only accessed by INSERT statement, and there were no updates done over it in application. One fine day, he ran distinct on both these columns and was surprised by the result. He always thought that both the tables will have the same data, but in fact, they had very different data.
He presented this scenario to me. I said this is not possible, but I had to agree with him when I looked at the resultset. Here is the simple script to demonstrate the problem he was facing. This is just a sample of the original table.
DECLARE @Intveral INT
SET @Intveral = 10000
CREATE TABLE #TimeTable (FirstDate DATETIME, LastDate DATETIME2)
WHILE (@Intveral > 0)
BEGIN
INSERT #TimeTable (FirstDate, LastDate)
VALUES (SYSDATETIME(), SYSDATETIME())
SET @Intveral = @Intveral - 1
END
GO
SELECT COUNT(DISTINCT FirstDate) D_GETDATE, COUNT(DISTINCT LastDate) D_SYSGETDATE
FROM #TimeTable
GO
SELECT DISTINCT a.FirstDate, b.LastDate
FROM #TimeTable a
INNER JOIN #TimeTable b ON a.FirstDate = b.LastDate
GO
SELECT *
FROM #TimeTable
GO
DROP TABLE #TimeTable
GO
Let us see the resultset.
You can clearly see from result that SYSDATETIME() does not populate the same value in both the fields. In fact, the value is either rounded down or rounded up in the field which is DATETIME. Eventhough we are populating the same value, the values are totally different in both the columns, thus resulting in the SELF JOIN fail and displaying of different DISTINCT values.
The best way is to use GETDATE() if you are using DATETIME, and SYSDATETIME() if you are using DATETIME2 to populate them with current date and time for accurately addressing the precision. As DATETIME2 is introduced in SQL Server 2008, the above script will only work with SQL Server 2008 and later versions.
I hope I have answered some of the questions asked yesterday.
Reference: Pinal Dave (http://www.SQLAuthority.com)
16 Comments. Leave new
Hi,
Sorry but I don’t quite follow. I think this is normal behavior since FirstDate isn’t as precise as LastDate and so it needs to be rounded.
Situation is almost the same as converting large number like FLOAT to smaller type like INT. You need to get rid of the “extra” bits in FLOAT in order to make it fit into INT. Plus you need to round up any decimals.
Joining works if you explicitly cast LastDate to DATETIME. I’m not sure why it can’t work like that implicitly, maybe there’s some deeper reason I don’t understand. But anyway I think it’s a common problem when comparing two different data types, which vary in size, together.
Btw. Does anyone know how Sql Server gets SYSDATETIME? Does it use performance counter? I think it has to because otherwise it can’t get so precise timestamps from Windows.
Silly of me. Of course there is implicit data type conversion happening in join. It’s just from smaller data type to larger one. Now because LastDate contains more precise value (more decimals you could say) it is different from FirstDate which just gets padded by extra zeros.
Funny what comes to mind when watching cartoons at morning :)
in condition use — convert(varchar(10),firstdate,103)=convert(varchar(10),lastdate,103)
No, use cast(firstdate as datetime) = cast(lastdate as datetime). Should be much more efficient than converting to string format.
HI,
Correct me if I am wrong, There is another dimension of that is the precision on the datetime has 3 microseconds difference at the date change then the date searched with,
I mean if I do SELECT CAST(‘2014-03-03 23:59:59.999’ AS DateTime) it returns the next day’s date, and if I do it in with ‘2014-03-03 23:59:59.998’ its returns me ‘2014-03-03 23:59:59.997’. My point is if you see the resultset and add those 3 Microseconds in DateTime2 field will make it possible to get the round figure correctly.
I hope i didn’t taken you off the subject here.
Thanks
Hello sir
I have question regarding datetime datatype.I have one view.I execute it with filter on date.But when i run it for perticular period it slows down .For ex. If I select period 1-Apr-2009 to 31-Mar-2010 then it runs very fine but if i select period 1-Mar-2010 to 31-Mar-2010 then it becomes too slow, I tried a lot to understand the problem but could not.Can you give me the explanation?
First, have you indexed the column? If not then you could gain significant performance boost from indexing.
Second, how much rows there are between 1-Apr-2009 to 31-Mar-2010 and how much rows are between 1-Mar-2010 and 31-Mar-2010? It could be that there’s a lot of rows in the periods that are causing problems and the DB has busy time reading them from the file(s).
Something you could do is to set statistics for io on and run both queries. See what results they produce. Use MSDN to analyze those results.
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statistics-io-transact-sql?view=sql-server-2017
Hi,
Check your indexes, run queries with to get the execution plan. Check what kind of seeks/scans are performed and tune your queries. Also look at the table indexes, are they right ones based on the data/user requirements.
Good points on what kind of date functions to use with DATETIME and DATETIME2 datatypes. Looks like really time sensitive applications/data could benefit from DATETIME2 data type.
hi Pinal
I have executed the same on sql server 2008 and found that
1- First query returned same results for both columns. Results is below:-
D_GETDATE D_SYSGETDATE
84 84
2- Joins are also working fine.
FirstDate LastDate
2010-07-07 06:55:32.527 2010-07-07 06:55:32.5270000
2010-07-07 06:55:34.777 2010-07-07 06:55:34.7770000
2010-07-07 06:55:34.027 2010-07-07 06:55:34.0270000
Thanks.
Ashutosh
Hi Pinal,
It’s so happy to read this your blog. 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 (implict or explict) 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. I use it in my work.
Hopes it helps to the developers met same problem.
Jing
—————————————————————
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
SELECT COUNT(DISTINCT FirstDate) D_DATETIME,
COUNT(DISTINCT LastDate) D_DATETIME2,
COUNT(DISTINCT GlobalDate) D_SYSGETDATE
FROM #TimeTable
GO
SELECT DISTINCT a.FirstDate,b.LastDate, b.GlobalDate,CAST(b.GlobalDate AS DATETIME)
FROM #TimeTable a
INNER JOIN #TimeTable b ON a.FirstDate =CAST (b.GlobalDate AS DATETIME)
GO
SELECT *
FROM #TimeTable
GO
DROP TABLE #TimeTable
GO
how can i insert the date difference in to int columns
Dear all, Without using lead & lag function how can I get the date difference like below sample in sql server 2005 / 2008 ?
Trn_Date Date_Diff
31/01/2016 0
03/02/2016 3
05/02/2016 2
07/02/2016 2
18/02/2016 11
25/02/2016 7
03/03/2016 7
Hello,
I have a question concerning the difference between datetime and datetime2. I shall be very thankful if you could kindly give me some insights. The question is:
?? Datetime2 has larger range and more accuracy than datetime. But still datetime2 requires less storage space as compared to datetime. What is the reason for this?
Best,
Madhur
OR,
SELECT CAST(SYSDATETIME() AS DATETIME) AS Date1, CAST(sysdatetime() AS DATETIME2) AS Date2