To get the current system date and time we can use GETDATE() or SYSDATETIME() function in SQL server.
The question is what the difference between them is.
To get the answer let execute the SQL statements mentioned bellow.
SELECT GETDATE() 'GETDATE', SYSDATETIME() 'SYSDATETIME'
The output is mentioned bellow:
GETDATE SYSDATETIME
2012-05-14 18:05:04.720 2012-05-14 18:05:04.7232705
So exactly what the difference is?
When we use GETDATE() the precision is till milliseconds and in case of SYSDATETIME() the precision is till nanoseconds.
SYSDATETIME() is an important when using the data type DATETIME2.
The data type DATETIME2 stores dates and times in a higher precious than the (old) data type DATETIME and therefore the difference between GETDATE() and SYSDATETIME() is important when using DATETIME2.
Conclusion
So there is really no reason to keep on using GETDATE(). And therefore the simple advice is: in SQL Server 2008 always use SYSDATETIME() when you want to retrieve the current date and/or time.
Hope you like it.
Posted by: MR. JOYDEEP DAS
Do all the other existing SQL Server date time functions accept the SYSDATETIME data type as an argument as well?
ReplyDeleteFirst of all I am thanking you “aiguy” to give your valuable time on it.
DeleteCan you please explain me WHY ?
There are very few applications where nanoseconds matter (or where even 1 millisecond matters). GetDate( ) is fine for 99.99% of all applications. And why take up more database space with a larger data type when not needed.
ReplyDeleteI'll agree that there are some applications where nanoseconds matter. I'm not sure Windows servers can even measure clocks in nanoseconds; I believe Windows servers' internal clocks run on a too coarse a frequency for nanoseconds to matter. Correct me on that if I'm wrong.
The statement that Microsoft recommends using DATETIME2 over DATETIME seems just out of touch with practicality. Did Microsoft really say that?
First of all I am thanking you “JazzHarmonicat” to give your valuable time on it.
DeleteYou are talking related to Implementation point of view.
Here Microsoft provide a technology related to work with neon second. Is it not good? I am not personally work with any nanosecond related work, but that’s not means that it is out of scope of any domain. As I have limited knowledge related to any scientific domain or any high hand calculation related with neon seconds I am not going to comments it.
For me it is a technology where we can use the neon second with DATETIME2 data type.
Once again thinking you.
Personally I would use the UTC alternatives to GETDATE (GETUTCDATE) and SYSDATETIME (SYSUTCDATETIME) and handle timezone offsets elsewhere but I digress.
DeleteI operate a large platform and for all the times where I need to store a DateTime, there is but once instance where I could go to the 100 nanosecond precision making 99.999% requirement to use datetime. Oracle has had TIMESTAMP which can go to 9 digit precision for much longer than SQL Server but across both platforms the need to go finer than second precision is pretty small. Just saying to use DATETIME2 for all needs is like saying use VARCHAR(MAX) for all text fields even if you only ever expect 4 chars. IIt's crazy! Once good thing however is that DATETIME2 doesn't consume any more space than DATETIME (8 bytes), but to get that 100 nanosecond precision it may have a cost performance for high transactional systems.
Moral of the story, however new and wonderful things may appear. The right tool for the right job will keep you fighting fit.
Thanks “Andy Furnival” for sharing your knowledge with us.
DeleteWhy use datetime2? Because it's more efficient. Datetime is always 8 bytes for 3.33 millisecond accuracy. Datetime2 can be 6-8 bytes. It doesn't cost you anything in terms of storage for the 100 nanosecond accuracy. 8 bytes or 8 bytes of storage. However, if you use only need 1 second of accuracy, then datetime2 is only 6 bytes of storage. More efficient. DATETIME2 stores dates and times independently internally. 3 bytes of the date (which can also store dates earlier than 1/1/1753 to 1/1/1 instead - not of great use, but there are uses), and 3-5 bytes to store just the time.
Delete100 nanoseconds is 1 tick, which is the smallest time unit in Windows. There actually isn't any performance penalty for reading out the time in 100ns increments. If anything, there is performance cost to round to the nearest 3.33 ms and format it using the integer format that DATETIME uses. In practice, I have seen zero indication that GETDATE and SYSDATETIME perform any differently to each other.
In summary, SYSDATETIME() returns a DATETIME2 which does everything (everything!) that DATETIME does, just more efficiently, and does more. There are zero drawbacks to using SYSDATETIME unless you still have to run your code on SQL 2005 or earlier and plenty of advantages.
Your VARCHAR(MAX) argument is flawed as it's not the same. With the right setting, using a VARCHAR(MAX), but never storing more than 4 chars would be the same storage and would work the same as varchar(4), aside from allowing more than 4 characters, which may violate a data type constraint. Violating a constraint, though, clearly distinguishes VARCHAR(MAX) from VARCHAR(4) as not the same. VARCHAR(MAX) also isn't more efficient for the same number of characters, unlike DATETIME2 is to DATETIME (7 bytes for ms accuracy for datetime2 vs 8 bytes for 3.33 ms accuracy for datetime).
Also, can you guarantee that there will be no columns needing greater accuracy than 3.33 ms in the future, or that the accuracy of existing columns may not need to change in the future? Why not future proof your code and db by using a better than free new feature?
Sure 100ns isn't always required, but there are other advantages to using DATETIME2 and SYSDATETIME. I have for many years exclusively used DATETIME2 (or DATE or TIME where necessary) and SYSDATETIME (or SYSUTCDATETIME or SYSDATETIMEOFFSET if timezone is important), and never once regretted the choice, and often benefited from it.
I can see no situation where GETDATE (and DATETIME) is a better tool than SYSDATETIME (and DATETIME2), where there are situations where varchar(4) or char(4) are better than VARCHAR(MAX). Sometimes the new and wonderful things are truly new and wonderful and should completely replace the prior tool.
Thanks Steven! Very well explained!
DeleteThanks Joy. This will be much helpful for everyone who come across this post. Most of the time you use the SYSDATETIME function while writing code for stock market companies. Since every nano second is very useful.
ReplyDeleteThanks "Suman"
Delete