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
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.
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
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
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();
