CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlgetdate
Royi Namir
Royi Namir

Reputation: 148694

True getDate() while updating?

lets say i have very big table ( many rows)

and i have this sql cmd : 

update myTable set name='Royi' , dateUpadted=getDate()

again , many rows ... - time to update whole table : 3 min. ( already indexed)

my goal is :

each updated row should have its true ( seconds / miliseconds difference) getdate() !!!

so my desire result : 

name | dateUpadated
___________________
royi |      12:00:00  -- getDate() here was 12:00:00
...  |         ...
...  |         ...
...  |         ...
royi |      12:01:13  -- getDate() here was 12:01:13
...  |         ...
...  |         ...
...  |         ...
royi |      12:03:01  -- getDate() here was 12:03:01

how can I do it ?

( I dont want to us cursor)

is there any switch which says to getDate() function :

" use your current value and not the one which youve started with " ?

Upvotes: 1

Views: 3080

Answers (2)

Martin Smith
Martin Smith

Reputation: 453707

You can wrap it inside a scalar UDF to get it to be re-evaluated for each row.

CREATE FUNCTION dbo.GETDATE()
RETURNS DATETIME    
AS
BEGIN
    RETURN GETDATE();
END

Upvotes: 4

Sören Kuklau
Sören Kuklau

Reputation: 19940

It is the "true" value, though. The UPDATE happens in a transaction; changes are committed in one go rather than per row. If you want rows updated individually, a cursor is exactly what you want.

Upvotes: 0

Related Questions