Question about GETDATE()

Question

Question about GETDATE()

Raymond van Laake

SSCarpal Tunnel

Points: 4212
More actions
January 20, 2012 at 1:45 am

#254025

Hi,
I need to set a date column, and milliseconds are significant.
If I do this:
UPDATE mytable SET colDate = GETDATE()
and lets say there are many rows and the server is a bit slow resulting that this query takes 1 second to complete, will all the records get the same datetime value or not?
I know I can do this:
DECLARE @now DATETIME = GETDATE()
UPDATE mytable SET colDate = @now
but I would prefer the above if I can be sure the datetime is the same for all records
Thanks,
Raymond

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Yes, they will. GetDate, like Rand (without a seed), gets one value for a statement (if called once)

That said, if milliseconds are significant, datetime is a poor data type to be using (rounds to 3 milliseconds), consider an appropriate precision datetime2 instead.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Robert Murphy UK1 Default port Points: 1485 More actions · Answer 2

If you are going to reuse the GETDATE() result for multiple statements then you must assign it's value to a variable as you are proposing. For 2 reasons:

1. Performance - reading a variable is faster than calling a function each time.

2. Accross statements in the same batch GETDATE() will return a different value, which is your concern.

Robert Murphy, Microsoft MCITP (Database Administrator 2008) and MCAD Certified

Raymond van Laake SSCarpal Tunnel Points: 4212 More actions · Answer 3

Thanks for your replies.

I actually need it for the following: because I must run the query on a table I don't know the name of beforehand, I must use a dynamic query. Currently I have this:

SET @fetchTimestamp = GETDATE()

DECLARE @strTimestamp CHAR(23) = RTRIM(CONVERT(CHAR,@fetchTimestamp,105))+ ' '+RTRIM(CONVERT(CHAR,@fetchTimestamp,114))

SET @sql = N' SET DATEFORMAT dmy UPDATE '+@myTable+' SET myTimeStamp='''+@strTimestamp+'''

EXEC sp_executesql @sql

Now I can use:

SET @sql = N' SET DATEFORMAT dmy UPDATE '+@myTable+' SET myTimeStamp=GETDATE()'

EXEC sp_executesql @sql

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

GilaMonster (1/20/2012)
Yes, they will. GetDate, like Rand, gets one value for a statement (if called once)

Not necessarily true for RAND, if it is seeded with a non-deterministic function like NEWID:

SELECT

GETDATE(),

RAND(CHECKSUM(NEWID())),

RAND(CHECKSUM(NEWID()))

FROM

(

SELECT 1

UNION ALL SELECT 2) AS Two (rows)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Raymond van Laake (1/20/2012)
Currently I have this:
SET @fetchTimestamp = GETDATE()
DECLARE @strTimestamp CHAR(23) = RTRIM(CONVERT(CHAR,@fetchTimestamp,105))+ ' '+RTRIM(CONVERT(CHAR,@fetchTimestamp,114))
SET @sql = N' SET DATEFORMAT dmy UPDATE '+@myTable+' SET myTimeStamp='''+@strTimestamp+'''
EXEC sp_executesql @sql

Why are you storing a date & time in a string? Shouldn't the 'myTimeStamp' column be defined as DATETIME or DATETIME2?

USE tempdb

GO

CREATE TABLE dbo.Test (myTimeStamp datetime NULL)

GO

INSERT dbo.Test

(myTimeStamp)

VALUES

(NULL), (NULL)

GO

DECLARE @dt datetime = GETDATE();

DECLARE @myTable sysname = N'[dbo].[Test]'

DECLARE @sql nvarchar(4000) =

N'

UPDATE ' + @myTable + '

SET myTimeStamp = @1';

EXECUTE sys.sp_executesql

@statement = @sql,

@params = N'@1 datetime',

@1 = @dt;

SELECT * FROM dbo.Test AS t

GO

DROP TABLE dbo.Test

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

SQL Kiwi (1/20/2012)
GilaMonster (1/20/2012)
Yes, they will. GetDate, like Rand, gets one value for a statement (if called once)
Not necessarily true for RAND, if it is seeded with a non-deterministic function like NEWID:
SELECT
GETDATE(),
RAND(CHECKSUM(NEWID())),
RAND(CHECKSUM(NEWID()))
FROM
(
SELECT 1
UNION ALL SELECT 2) AS Two (rows)

True, should have clarified - Rand without a seed.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Raymond van Laake SSCarpal Tunnel Points: 4212 More actions · Answer 7

Hi Paul,

You are completely right.... I rewrote my code per your advise and it works like a charm.... thanks a lot!

Raymond

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 8

Robert Murphy UK1 (1/20/2012)
If you are going to reuse the GETDATE() result for multiple statements then you must assign it's value to a variable as you are proposing. For 2 reasons:
1. Performance - reading a variable is faster than calling a function each time.

Not exactly true where GETDATE() is concerned. GETDATE() is only calculated once per query and is stored in a "run time variable" behind the scenes. That makes it faster than what you suggest.

Of course, me saying so doesn't make it so. So, here's some code to play with.

DECLARE @SomeDateTime DATETIME;

SELECT @SomeDateTime = GETDATE();

SET STATISTICS TIME ON;

SELECT TOP 2000000

SomeDateTime = GETDATE()

INTO #Race1

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

SELECT TOP 2000000

SomeDateTime = @SomeDateTime

INTO #Race2

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

SET STATISTICS TIME OFF;

DROP TABLE #Race1, #Race2;

I ran that code 10 times and came up with results similar to the following each time.

SQL Server Execution Times:

CPU time = 1375 ms, elapsed time = 1907 ms.

(2000000 row(s) affected)

SQL Server Execution Times:

CPU time = 1563 ms, elapsed time = 2127 ms.

(2000000 row(s) affected)

--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)