GetDate() or CURRENT_TIMESTAMP

Question

GetDate() or CURRENT_TIMESTAMP

Viewing 8 posts - 46 through 52 (of 52 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

vinothraj (12/22/2010)
Jeff Moden (12/22/2010)
Which code did you use for that test? If the answer is that you used the same code that you used before, the reason why it differs is because it takes more time to store the word "getdate" than it does to store the word "ct".
Just used gd and ct however.. Keen to know one more thing, getdate() is a method / function, but what is current_timestamp? want to know, whether getdate() calling current_timestamp or just building the current date time..

CURRENT_TIMESTAMP is nothing but an alias for GETDATE() and you can see the translation in the execution plan.

Please post the code you used for your latest test.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

It is always fun to see debates going on for a topic 3 months after the original post.

For my part, since there is no real difference between the 2 I converted to CURRENT_TIMESTAMP, but still catch myself using getdate() out of year of habit.

This is definitely not something I would argue with anyone about. If I was setting work place standards this is one where I'd allow either. It's not like .NET with DateTime.Today and DateTime.Now as they are 2 different things.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

vinothraj Ten Centuries Points: 1349 More actions · Answer 3

Correct Jack, but its a discussion place and we are discussing it.,.. We did discussed some good information and also i benefited from new answers... 😉

Current_timestamp is just a alias to getdate(), so we can happily ended with getdate is good.. and obviously most of the persons saying that have very less typo...

If everyone is OK with the above statement we shall close this.. 😀

Toby Harman SSCarpal Tunnel Points: 4168 More actions · Answer 4

Taking into consideration All the above posts, what about the 3rd option.

SYSDATETIME()

Advantages: Returns a DATETIME2

Disadvantages: ?

Personally, I prefer GETDATE() because there is also GETUTCDATE(). I don't believe there is an equivalent CURRENT_TIMESTAMP function

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Toby Harman (12/29/2010)
Taking into consideration All the above posts, what about the 3rd option.
SYSDATETIME()
Advantages: Returns a DATETIME2
Disadvantages: ?
Personally, I prefer GETDATE() because there is also GETUTCDATE(). I don't believe there is an equivalent CURRENT_TIMESTAMP function

Disadvantages: works with version >= 2008.

According to BOL all of these return the same result and can be assigned to any of the date datatypes:

SELECT SYSDATETIME()

,SYSDATETIMEOFFSET()

,SYSUTCDATETIME()

,CURRENT_TIMESTAMP

,GETDATE()

,GETUTCDATE();

-- Gianluca Sartori

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 6

I use getdate() purely as a habit.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Paul White SSC Guru Points: 150467 More actions · Answer 7

Gianluca Sartori (12/30/2010)
According to BOL all of these return the same result and can be assigned to any of the date datatypes:
SELECT SYSDATETIME()
,SYSDATETIMEOFFSET()
,SYSUTCDATETIME()
,CURRENT_TIMESTAMP
,GETDATE()
,GETUTCDATE();

CURRENT_TIMESTAMP and GETDATE() are exactly the same, but the others all differ either in one or more of {return value, return type, precision}.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

You're right, Paul (as usual ;-)).

From BOL:

GETDATE (Transact-SQL)
Note:
SYSDATETIME and SYSUTCDATETIME have more fractional seconds precision than GETDATE and GETUTCDATE. SYSDATETIMEOFFSET includes the system time zone offset. SYSDATETIME, SYSUTCDATETIME, and SYSDATETIMEOFFSET can be assigned to a variable of any of the date and time types.
...
The following examples use the six SQL Server system functions that return current date and time to return the date, time, or both. The values are returned in series; therefore, their fractional seconds might be different.
SELECT SYSDATETIME()
,SYSDATETIMEOFFSET()
,SYSUTCDATETIME()
,CURRENT_TIMESTAMP
,GETDATE()
,GETUTCDATE();

-- Gianluca Sartori