Set a DateTime database field to "Now"

Question

In VB.net code, I create requests with SQL parameters. It I set a DateTime parameter to the value DateTime.Now, what will my request look like ?

UPDATE table SET date = "2010/12/20 10:25:00";

or

UPDATE table SET date = GETDATE();

In the first case I'm sure that every record will be set to the exact same time. In the second case it depends on how the DBMS processes the request. Which leads me to the second question : does SQL Server set the same date and time when updating a large table with NOW() ?

EDIT : replaced NOW() (which doesn't exist in SQL Server) by GETDATE().

score 205 · Accepted Answer · edited Dec 16 '13 at 08:20

205

In SQL you need to use GETDATE():

UPDATE table SET date = GETDATE();

There is no NOW() function.

To answer your question:

In a large table, since the function is evaluated for each row, you will end up getting different values for the updated field.

So, if your requirement is to set it all to the same date I would do something like this (untested):

DECLARE @currDate DATETIME;
SET @currDate = GETDATE();

UPDATE table SET date = @currDate;

edited Dec 16 '13 at 08:20

bluish

26,356
27
122
180

answered Dec 20 '10 at 09:34

Oded

489,969
99
883
1,009

Oops yes NOW() is Mysql, sorry. But the questions remains. – Thibault Witzig Dec 20 '10 at 09:38
Ok thanks. So I need to be sure to put the actual date (from code) in my request dans not GETDATE() Do you know it setting the SQLparameter to DateTime.Now will do this or If I should first convert the date to a string and then add it to the request ? – Thibault Witzig Dec 20 '10 at 09:41
1

@@Thibault Witzig - You could. Or you could use the SQL I have posted (get the current date into a variable and use the variable to set the date in the table - the value in the variable will not change). – Oded Dec 20 '10 at 09:44
If you want to keep the same time for all records, pass Now in as a parameter using Parameterized SQL. That way, you don't have to worry about string parsing or localized data string formatting issues. – Jim Wooley Jul 17 '13 at 14:31

score 29 · Answer 2 · answered Jul 17 '13 at 12:09

29

An alternative to GETDATE() is CURRENT_TIMESTAMP. Does the exact same thing.

answered Jul 17 '13 at 12:09

Don Simon

591
5
17

10

`CURRENT_TIMESTAMP` is actual the SQL Standard so some might argue this is the preferred syntax. – Tony L. Sep 08 '17 at 17:09
This is quite wrong. I just read the docs on those two and it appears obvious to me that those aren't "exact the same thing". This question offers a more detailed explanation for this: https://stackoverflow.com/questions/7105093/difference-between-datetime-and-timestamp-in-sqlserver – Mladen B. Feb 28 '19 at 11:37

score 7 · Answer 3 · answered Dec 20 '10 at 09:34

Use GETDATE()

Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.

UPDATE table SET date = GETDATE()

score 0 · Answer 4 · answered Dec 28 '22 at 04:07

0

If you are adding a row to the table and need one value to be updated with the current date and time use - now() eg. insert into actor values (202,'ALIA', 'BHATT',now());

answered Dec 28 '22 at 04:07

Som

1
3

Set a DateTime database field to "Now"

4 Answers4