Clever Geek Handbook

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

+2


source to share


5 answers


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

+5


source


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 .

+6


source


http://doc.ddart.net/mssql/sql70/ga-gz_1.htm

0


source


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

0


source


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


source






More articles:


All Articles