July 13, 2011 at 4:05 am
Hi
I googled and everybody is saying that there is no difference between the CURRENT_TIMESTAMP and GETDATE(), only that CURRENT_TIMESTAMP is ANSI SQL and is more of a standard than GETDATE(). So just to make sure is this correct?
The curiosity came when I found the statement below
SELECT SYSDATETIME() as [SYSDATETIME]
,SYSDATETIMEOFFSET() as [SYSDATETIMEOFFSET]
,SYSUTCDATETIME() as [SYSUTCDATETIME]
,CURRENT_TIMESTAMP as [CURRENT_TIMESTAMP]
,GETDATE() as [GETDATE]
,GETUTCDATE() as [GETUTCDATE];
Everything returns the same results if your UTC Offset is 0. Also what's the difference between the SYSDATETIME() and GETDATE().
There seems to be a lot of functions that all do the same thing and I just wondering if there's reasons for all of this.
Thanks in advance. 😀
The Fastest Methods aren't always the Quickest Methods
July 13, 2011 at 4:52 am
Per Books Online:
SYSDATETIME and SYSUTCDATE have more fractional seconds precision than GETDATE and GETUTCDATE
It's all in how detailed you want to get with your datetime value.
As far as the Current_Timestamp() goes, I'm guessing it was added for those who are serious about ANSI compliance and have hetrogeneous systems that they need to move data between. There really isn't (so far as I can see) any difference but a matter of comfort in usage.
I like GetDate() because it's faster to type.
July 13, 2011 at 4:59 am
No wonder my friend! It been a while Microsoft has come up with new versions and keeping few old features for backward compatibility.
There is no difference in CURRET_TIMESTAMP / GETDATE() / SYSDATETIME(). all these function returns the date time of machine running SQL Server.
Abhijit - http://abhijitmore.wordpress.com
July 13, 2011 at 5:04 am
Thanks 🙂
The Fastest Methods aren't always the Quickest Methods
July 13, 2011 at 5:08 am
Abhijit More (7/13/2011)
There is no difference in CURRET_TIMESTAMP / GETDATE() / SYSDATETIME(). all these function returns the date time of machine running SQL Server.
Careful with that assumption. Since SysDateTime is more precise, it has more digits and could cause conversion errors if you try to downsize it into a column that was designed for GetDate().
Therefore, there is a difference between SysDateTime() and the other two.
July 24, 2011 at 11:32 am
It's taken me a long time to wean myself of querying sysobjects, using GETDATE() etc.
I'm finding I'm dealing with disparate data sources so focussing on ANSI standard commands does eventually make life easier.
I've dealt with 3 or 4 platforms that support some form of implementation of INFORMATION_SCHEMA and the big MPP platforms support CURRENT_TIMESTAMP.
I'll admit it was hard to change. I suspect cloud computing is going to get us all thinking about platforms other than the ones we have in house.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply