May 1, 2012 at 2:21 am
Hello,
Can anyone please explain the difference between GETDATE() and CURRENT_TIMESTAMP ?
I'm sure there must be, but I am not certain what it is and where each is best used.
Any help appreciated.
Regards
Steve
May 1, 2012 at 2:33 am
There no performance difference between the two.
CURRENT_TIMESTAMP is the ANSI SQL equivalent to GETDATE().
There's post discussing this which you may find useful.
http://www.sqlservercentral.com/Forums/Topic991342-373-1.aspx
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
May 1, 2012 at 2:38 am
CURRENT_TIMESTAMP is the ANSI standard method for getting date & time while GETDATE() is the T-SQL method.
Also GETDATE() returns the datetime specific to the database including the database time zone offset where as CURRENT_TIMESTAMP doesn't include the database offset.
Edit: No difference in terms of performance.
May 1, 2012 at 2:48 am
vinu512 (5/1/2012)
Also GETDATE() returns the datetime specific to the database including the database time zone offset where as CURRENT_TIMESTAMP doesn't include the database offset.
What do you mean by that?
Getdate and current_timestamp return the same thing, the current time on the database server, regardless of what timezone the server is set to. It's GETUTCDATE() that returns the GMT (UTC) time and SYSDATETIMEOFFSET that returns the time with the timezone offset information
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2012 at 2:57 am
GilaMonster (5/1/2012)
vinu512 (5/1/2012)
Also GETDATE() returns the datetime specific to the database including the database time zone offset where as CURRENT_TIMESTAMP doesn't include the database offset.What do you mean by that?
Getdate and current_timestamp return the same thing, the current time on the database server, regardless of what timezone the server is set to. It's GETUTCDATE() that returns the GMT (UTC) time and SYSDATETIMEOFFSET that returns the time with the timezone offset information
:blush::blush: uhmm!!....Sorry got myself tangled a bit with the various date versions.
May 2, 2012 at 8:08 am
They do the same thing. Probably even use the exact same binaries behind the scenes where SQL Server does all the actual work.
So use CURRENT_TIMESTAMP if you can. It's a good habit, in case GETDATE() goes the way of *= or any of a number of other non-ANSI T-SQL extensions. Since there's no advantage of one over the other in terms of actual use, CURRENT_TIMESTAMP has a slight edge in terms of future-proofing code. Very slight, but it exists.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 1, 2014 at 10:15 am
I use SYSDATETIME().
It is ANSI/ISO compliant (supposedly, I only found one reference at http://sqlhints.com/tag/datetime-vs-datetime2/).
SYSDATETIME() adds a level of precision useful if I'm logging transactions and DATETIME2(n) datatype can use less storage space if you specify the precision appropriately. It's only available starting in SQL Server 2008 and can populate the DATETIME2 data type.
If I need to work with and older database I use CURRENT_TIMESTAMP or, if the DBA doesn't like CURRENT_TIMESTAMP, I use GETDATE().
SELECT
SYSDATETIME() [SYSDATETIME],
CURRENT_TIMESTAMP [CURRENT_TIMESTAMP],
GETDATE() [GETDATE]
Results:
SYSDATETIME
2014-10-01 10:46:09.8724461
CURRENT_TIMESTAMP
2014-10-01 10:46:09.870
GETDATE
2014-10-01 15:46:09.870
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply