GetDate() or CURRENT_TIMESTAMP

Question

Post reply

GetDate() or CURRENT_TIMESTAMP

Jack Corbett

SSC Guru

Points: 184393
More actions
September 22, 2010 at 10:19 am

#224290

You can even leave a comment as to why. My habit is GetDate(), but I'm converting to CURRENT_TIMESTAMP as I think it is clearer.
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

Viewing 15 posts - 1 through 15 (of 52 total)

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

I've yet to come across with any reason to not use GetDate() so I'm sticking with GetDate().

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Derrick Smith SSChampion Points: 10833 More actions · Answer 2

CURRENT_TIMESTAMP is the ANSI way to do it..so I guess if you're into such things, go with that.

I use getdate() because I've been using it for roughly a hundred years now, and I probably couldn't break the habit if I tried.

Both commands execute the same internal SQL command, so there is no performance/result difference either way.

Lowell SSC Guru Points: 323504 More actions · Answer 3

i use getdate() excluseively; like others havre said, it's been in my repitoir for years, and also because of that datatype where a timestamp is not really a timestamp, but it's a rowversion indicator instead, i think the variable current_timestamp is "contaminated", so i avoid using it and stick with getdate() function.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

vinothraj Ten Centuries Points: 1349 More actions · Answer 4

I would like to use getdate() mostly as its having frequent hits than current_timestamp. So far i couldnt see any objections with getdate() and its user friendly.

LadyRuna SSCrazy Points: 2312 More actions · Answer 5

I use CURRENT_TIMESTAMP most often, but if I'm feeling lazy about typing, I'll use getdate() because it's fewer characters to type. 🙂

In years past, I'd always used getdate(), but I'd read that CURRENT_TIMESTAMP was supposedly "better", so I've switched over. I'm not certain about the truth in that, but I do like the idea of at least attempting to follow ANSI standards.

fabricioaraujorj SSC Journeyman Points: 88 More actions · Answer 6

If there's a standard (be it de-facto or formal) way and a non-standard way, and the non-standard give me no advantage I use the standard.

So I use CURRENT_TIMESTAMP.

GSquared SSC Guru Points: 260824 More actions · Answer 7

I tend to use GetDate() because of habit, but I have to admit, Current_Timestamp is more expressive of what it does, more intuitive to a person who isn't already indoctrinated into one or the other, and it can't hurt to follow the standard. I plan to switch over ... tomorrow ... or maybe ...

- 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

Johan Bijnens SSC Guru Points: 135259 More actions · Answer 8

I noticed my devs prefer getdate because they get confused by sqlservers timestamp data type and that same word in current_timestamp !

:blush:

I think that issue should have been fixed by microsoft years ago.

(rename that datatype !)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

WayneS SSC Guru Points: 95409 More actions · Answer 9

I think Microsoft prefers getdate().

CREATE TABLE #test (RowID INT IDENTITY, MyDate DATETIME DEFAULT (CURRENT_TIMESTAMP));

SELECT definition

FROM tempdb.sys.default_constraints

WHERE parent_object_id = object_id('tempdb..#test');

returns:

definition

-----------

(getdate())

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

vinothraj Ten Centuries Points: 1349 More actions · Answer 10

I've checked elapsing time for each of those and got approximate time is less for current_timestamp than getdate().

Is current_timestamp better? i think so....

vinothraj Ten Centuries Points: 1349 More actions · Answer 11

by the by,.... current_timestamp averages 0.21 seconds where as getdate() averages 0.234 seconds.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Having been using GETDATE() since I started using SQL Server, that is what I use. However, now that I am using Oracle, I have to change my ways. Will this affect how I code SQL Server, only time will tell.

tommyh SSCertifiable Points: 6302 More actions · Answer 13

vinothraj (12/16/2010)
by the by,.... current_timestamp averages 0.21 seconds where as getdate() averages 0.234 seconds.

I seriously doubt that it takes 0.21 SECONDS to run current_timestamp (or getdate() for that matter) one time.

Doing

declare @i integer, @d datetime

select @i = 1

while @i < 100000 select @d = getdate(), @i = @i + 1

takes about 0.5s. So 1 run would be something like 0.000005s. Well actually less since there is ALOT of extra code in my test besides the date thing.

/T

vinothraj Ten Centuries Points: 1349 More actions · Answer 14

I'm working in linked server but you might be working in your own. i just told it based upon the average of ten times but i would like to convey it seems to be current_timestamp conceives less time.