Insert server datetime using a SqlCommand

Question

I have a SqlCommand which inserts a datetime into column using a SqlParameter. At present I use DateTime.Now as the value of this parameter.

I believe this will add the datetime on the user's PC which is not consistent.

How do I change this so the datetime of the database server is inserted?

EDIT:

I should have made it clear this was just for inserts

Answer 1

Hi, http://doc.ddart.net/mssql/sql70/ga-gz%5F1.htm Best Regards, Iordan

Answer 2

Don't pass in a parameter for the current date; get SQL Server to generate it.

The SQL to do what you described is GETDATE(), though you may want to consider using GETUTCDATE(), as it will behave better with respect to timezone changes (and moving the server!)

See MSDN for more details.

Answer 3

In that case, don't pass a datetime value as a parameter. Instead use the GetDate() function and insert that result in the table

insert into X (Id, Col1, Col2) values (1, 'Customer1', GetDate())

Hope this helps

Answer 4

Is this strictly for INSERTs ? Or for updates as well?

If it's for INSERTs only, you could declare a DEFAULT constraint on your DATETIME column on the SQL Server side, and then just not insert a value from the client side - thus using the server's default.

In SQL:

 ALTER TABLE YourTable 
   ADD CONSTRAINT DF_YourTable_YourColumn
     DEFAULT (GETDATE()) FOR YourColumn

and then in your SQL command, just don't mention the "YourColumn" and don't provide a value for it - the server will use getdate() to get the current date/time on the server instead.

Marc

Answer 5

You may use stored-procedure and GETDATE(),

tableName(no: int, name: varchar(40), date datetime)

Stored procedure

CREATE PROCEDURE  AddRec
@no int,
@name varchar(40)
AS
insert into tableName
 values (@no,@name,getdate())
RETURN

Code to execute stored-procedure

SqlConnection cn=new SqlConnection("Your_Cn_Str");
SqlCommand cmd=new SqlCommand();
cmd.CommandText="AddRec";
cmd.CommandType=CommandType.StoredProcedue;
cmd.Connection=cn;

cmd.Parameters.AddWithValue("@no",10);
cmd.Parameters.AddWithValue("@name","Foo");

cn.Open();
cmd.ExecuteNonQuery();
cn.Close();