Insert server date and time using SqlCommand
I have a SqlCommand that inserts a datetime into a column using SqlParameter. I am currently using DateTime.Now as the value for this parameter.
I suppose this will add the datetime on the user's PC, which is inconsistent.
How do I change this to insert the date and time of the database server?
EDIT:
I had to make it clear that it was for inserts only
Is it strictly for INSERT? Or for updates?
If it's for INSERT only, you can declare a DEFAULT constraint on the DATETIME column on the SQL Server side and then just not insert the value from the client side - thus using the default server.
In SQL:
ALTER TABLE YourTable
ADD CONSTRAINT DF_YourTable_YourColumn
DEFAULT (GETDATE()) FOR YourColumn
and then in your SQL command just don't mention "Column" and don't specify its value - the server will use getdate()
to get the current date / time on the server.
Mark
Do not enter a parameter for the current date; get SQL Server to create it.
SQL to do what you described, GETDATE()
although you might consider using GETUTCDATE()
it as it will behave better with respect to timezone changes (and server moves!)
See MSDN for details .
http://doc.ddart.net/mssql/sql70/ga-gz_1.htm
In this case, do not pass the datetime value as a parameter. Use the GetDate () function instead and insert this result into your table
enter values X (Id, Col1, Col2) (1, 'Customer1', GetDate ())
Hope it helps
You can use stored procedure and GETDATE (),
tableName (none: int, name: varchar (40), date and time)
Stored procedure
CREATE PROCEDURE AddRec
@no int,
@name varchar(40)
AS
insert into tableName
values (@no,@name,getdate())
RETURN
Code to execute a 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();