CODEWITHSUNDEEP
.netsqlsql-serverado.net
Gary Joynes
Gary Joynes

Reputation: 656

Insert server datetime using a SqlCommand

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

Upvotes: 2

Views: 1594

Answers (5)

IordanTanev
IordanTanev

Reputation: 6240

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

Upvotes: 0

KV Prajapati
KV Prajapati

Reputation: 94635

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

Upvotes: 0

marc_s
marc_s

Reputation: 754348

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

Upvotes: 5

tzup
tzup

Reputation: 3604

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

Upvotes: 0

Dave Roberts
Dave Roberts

Reputation: 537

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.

Upvotes: 6

Related Questions