T-SQL coding

Some Tips on Using GETDATE

GETDATE…such an easy function. You can use it whenever you need the current date and time. I often tell developers that you should always declare a variable and set that variable to GETDATE, then reference that variable in your code. Why?

Most of the time when I see the GETDATE function in code, I see it multiple times. For instance, you write a stored procedure to insert rows into 3 tables and you want to store the “create date and time” on each row. Most developers will just use the GETDATE function in the 3 insert statements and be done with it…and most developers won’t even notice that the date and time might be slightly different in each table (or it could be the same, depending on the speed of the inserts!). I suggest that, as a best practice, that the developer always declare a variable and set it once for each distinct value of the current date and time that you want.

-- instead of this code...
INSERT into Table1 (my column list here...)
VALUES (GETDATE()...);

INSERT into Table2 (my column list here...)
VALUES (GETDATE()...);

INSERT into Table3 (my column list here...)
VALUES (GETDATE()...);

-- do it like this!
DECLARE @MyDateTime datetime = GETDATE();

INSERT into Table1 (my column list here...)
VALUES (@myDateTime...);

INSERT into Table2 (my column list here...)
VALUES (@myDateTime...);

INSERT into Table3 (my column list here...)
VALUES (@myDateTime...);

 

I know, I didn’t answer the question; why does GETDATE sometimes return different values for the current date and time? There are 2 reasons. First, SQL Server doesn’t update the current date and time instantly; it waits a short amount of time (somewhere north of 1 millisecond).  For example, if I run this script I consistently get the same timestamp returned:

select getdate();
waitfor delay '00:00:00:001';
select getdate(); 

Run this and note that both statements return the same value. But if I change the delay to 2 milliseconds I get different values. So, if two statements run very quickly (around 1 millisecond or less) you MAY get the same value for GETDATE, or you MAY NOT…your results WILL vary.

select getdate();
waitfor delay '00:00:00:002';
select getdate(); 

There is also a second reason. Starting with SQL Server 2005, GETDATE became a non-deterministic runtime constant scalar function. Basically this means that, although it is non-deterministic (meaning you can get a different value each time you run it), it is also treated like a constant for the life of your statement. So, for any single use of GETDATE within a given statement you should get one value for GETDATE. I’ll refer you to reason one if you decide to use GETDATE multiple times in the same statement; SQL will evaluate it twice, but the 1 millisecond “rule” applies). For good examples of this I’ll refer you to these 2 posts:

http://sqlblog.com/blogs/andrew_kelly/archive/2008/03/01/when-a-function-is-indeed-a-constant.aspx

http://stackoverflow.com/questions/12034738/evaluating-getdate-twice-in-a-statement-will-it-always-evaluate-to-be-the-same

When you combine the 2 reasons above you will sometimes get results that you don’t expect. If you have 2 statements and the first statement runs in 1 millisecond you could get the same timestamp in the second, but there are no guarantees…Therefore, I always suggest that you use a variable to hold your value, like a constant, and use that variable everywhere in your code. In addition, isn’t it just better if all of the rows involved in your business function have the EXACT same timestamp?

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s